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!

Answer 1

Just use a sub-select:

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

Answer 2

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

Answer 3

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

Answer 4

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;

Answer 5

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)