MySQL select SUM of results with a LIMIT

Question

I have a table full of items and prices for said items. I would like to grab the SUM of the 3 highest priced items.

I thought perhaps SELECT SUM(items.price) FROM items ORDER BY items.price LIMIT 3 but that does not seem to do the trick. Is this possible? Thanks!

score 45 · Accepted Answer · answered Mar 26 '12 at 18:48

45

select sum(price) from (select items.price from items order by items.price desc limit 3) as subt;

answered Mar 26 '12 at 18:48

KernelM

score 16 · Answer 2 · answered Mar 26 '12 at 18:50

16

LIMIT affects the number of rows returned by the query and SUM only returns one. Based on this thread you might want to try:

SELECT sum(price) 
FROM (SELECT price
      FROM items
      ORDER BY price DESC
      LIMIT 3
) AS subquery;

answered Mar 26 '12 at 18:50

ScottJShea

score 2 · Answer 3 · answered Mar 26 '12 at 18:49

2

Just use a sub-select:

select sum(prices) from (SELECT SUM(items.price) FROM items ORDER BY items.price LIMIT 3) as prices

answered Mar 26 '12 at 18:49

Dave Halter

score 1 · Answer 4 · edited Mar 26 '12 at 18:54

1

Use a subquery or something like that. Just an idea, as I have not tested the actual query.

SELECT SUM(items.price) from (select price FROM items ORDER BY items.price LIMIT 3)

edited Mar 26 '12 at 18:54

Wiseguy

answered Mar 26 '12 at 18:50

johnshen64

1

Right answer except that it seems when I write `SUM(items.price)` the query fails, but leaving out the table ie. `SUM(price)` the query works – PotatoFro Mar 26 '12 at 19:04

score -1 · Answer 5 · answered Mar 26 '12 at 18:59

-1

I haven't tested this and I just wrote it on my memory. You could try something like:

SELECT * AS total FROM items ORDER BY price DESC
SELECT SUM(price) FROM total LIMIT 3;

Edit: I was slow

answered Mar 26 '12 at 18:59

Mare

5 Answers5