Grouping with GROUP BY and HAVING
Aggregates on their own summarise the whole table. GROUP BY gives one summary row per group:
SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city;
| city | customers |
|---|---|
| Bulawayo | 2 |
| Harare | 3 |
| Mutare | 1 |
| Port Louis | 2 |
Every column in the SELECT must either be in the GROUP BY or be inside an aggregate. Asking for name here would make no sense: which name, out of the three in Harare?
HAVING filters groups
WHERE filters rows before grouping. HAVING filters the groups after they are summarised:
SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) >= 2;
The order of the parts is always: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT.
Your task
Show each product category and how many products it has (call the column products), but only categories with more than one product. Sort by products, biggest first, and then by category name.