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!
In MySQL the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments which are offset and count. The value of both the parameters can be zero or positive integers.
The MySQL LIMIT Clause The LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
COUNT() is used to count the number of rows for a given condition. COUNT() works on numeric as well as non-numeric values. SUM() is used to calculate the total sum of all values in the specified numeric column.
The LIMIT clause can restrict the result set of the query to some maximum number of rows. If this clause specifies a value smaller than the number of qualifying rows, the query returns only a subset of the rows that satisfy the selection criteria.
select sum(price) from (select items.price from items order by items.price desc limit 3) as subt;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With