The problem: filtering by an aggregate
What if you only want the categories with more than 2 products, or with an
average price above 100? You can't use WHERE, because WHERE looks row by
row, and at that point the group's aggregate doesn't exist yet.
That is what HAVING is for: it filters groups based on their aggregate value,
after applying GROUP BY.
SELECT category, COUNT(*) AS total
FROM products
GROUP BY category
HAVING COUNT(*) > 2;
WHERE vs HAVING (the key difference)
| Clause | Filters... | When it acts | Can it use aggregates? |
|---|---|---|---|
WHERE |
rows | before grouping | No |
HAVING |
groups | after grouping | Yes |
The logical order of execution is:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
That is why you can combine the two: WHERE discards individual rows before
grouping, and HAVING discards whole groups after.
-- Only products with stock, grouped by category,
-- keeping the categories whose average price is above 100
SELECT category, AVG(price) AS average_price
FROM products
WHERE stock > 0
GROUP BY category
HAVING AVG(price) > 100
ORDER BY category;
Rule of thumb: if the condition can be evaluated by looking at a single row, it goes in
WHERE; if it needs the group's result, it goes inHAVING.