WHERE or HAVING: the difference in one example

Both filter, but at different moments. A store example is enough to never mix them up again.

WHERE and HAVING are both used to filter, which is why they get confused. The key is when each one acts: WHERE filters rows before grouping; HAVING filters groups after grouping.

The bike shop

We have a sales table with store and amount. We want the stores that have brought in more than $5,000, but counting only sales over $100.

SQL

SELECT store, SUM(amount) AS total
FROM sales
WHERE amount > 100           -- 1) remove individual rows
GROUP BY store               -- 2) group
HAVING SUM(amount) > 5000;   -- 3) remove groups

•WHERE can't use SUM, COUNT or AVG: the groups don't exist yet.
•HAVING almost always includes a summary function.
•If a condition can go in WHERE, put it there: the query works with fewer rows.

🦉 Tip: A trick to remember it: WHERE looks at each row, HAVING looks at each group.

Try it now?

Practice with the “Summarize and clean” module →

Keep reading