odd query

[AV1611]

let's say you had a table with 2 fields:

 

item    qty

a          3

b          17

c          4

d          1

e          1

f          2

 

 

Now I need to do something strange:

 

I need to select the 3 max qty's i order (that's easy) then I need to give the total of all the rest as a single qty.

 

so I would end of with:

 

item    qty

b          17

c          4

a          3

other    4

 

Can I do it with just queries?  Hint?  Anything would help at this point before I write a really LOOOOONG script to do it in my caveman style script writing

 

(Q: can one do a query that select 4th largest or lower?)

 

Thanks.

[micah1701]

>(Q: can one do a query that select 4th largest or lower?)

 

SELECT * FROM table ORDER BY qty LIMIT 4

[Philip]

Wait, I know micah1701's query would work, but are you asking to have it start on the 4th largest and go down, or end on the 4th largest?

[Barand]

SELECT 'other' as item, SUM(i.qty) as qty FROM  items i
LEFT JOIN (SELECT item FROM items ORDER BY qty DESC LIMIT 3) as x
ON i.item = x.item
WHERE x.item IS NULL