Rank the groups
Sort groups by their total and keep the top ones, minding ties. About 9 minutes.
Sort by the total
"Which item sells the most?" is three steps in a row. Group by item. Work out each group's total. Sort the groups by that total, biggest first, and keep the first one or few.
ORDER BY can use an aggregate, or the name you gave it with AS. LIMIT then keeps the top groups.
SELECT item, SUM(qty) AS sold FROM sales GROUP BY item ORDER BY sold DESC LIMIT 2;
| id | item | qty |
|---|---|---|
| 1 | Pen | 4 |
| 2 | Book | 2 |
| 3 | Pen | 3 |
| 4 | Bag | 1 |
| 5 | Book | 7 |
| 6 | Bag | 1 |
Pen: 4 + 3 = 7 sold.
Ties hide behind LIMIT
If two items sold the same amount, ORDER BY sold DESC puts them next to each other, and LIMIT 1 keeps just one. Which one? The database may pick either, and it can change from run to run.
Decide on purpose. Add a second ORDER BY column, such as the name, so the same one always wins. If tied groups should all show, compare each total with the highest total instead.
ORDER BY sold DESC, item LIMIT 1
Two items both sold 9. What does ORDER BY SUM(qty) DESC LIMIT 1 return?
- AOne of the two, and which one isn't guaranteed
- BBoth of them
- CNeither, because they tie
Show the answer
One of the two, and which one isn't guaranteed. LIMIT 1 keeps a single row, and nothing says which of the tied rows comes first.
Best seller
The sales table has one row per sale of an item. Show the item with the greatest total quantity sold, and that total as sold. If two items tie, take the one that comes first A to Z.
sales(id, item, qty)
Up to 2,000 sales