MySQL select SUM of results with a LIMIT

问题:

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!

回答1:

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

回答2:

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;

回答3:

Just use a sub-select:

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

回答4:

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)

回答5:

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