Lessons · SQL · filtering the groups, not the rows

Filtering groups

WHERE filters rows before grouping; HAVING filters groups after, by their aggregate. HAVING COUNT(*) >= 2 keeps the groups with at least two rows.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Customers with more than one order, cities with at least ten users, products reviewed five times: questions about groups need HAVING, because the count does not exist until the grouping has happened.

How to think about it

Group first in your head, then ask what test applies to each group. If the test mentions COUNT, SUM or AVG, it belongs in HAVING.

Worked example

SELECT customer_id, COUNT(*) AS n

One row per customer, with a count.

FROM orders

The rows.

GROUP BY customer_id

Collapse to groups.

HAVING COUNT(*) >= 2;

Keep only the groups with two or more orders.

Your turn

Cities with at least ten customers.

SELECT city FROM customers GROUP BY city  COUNT(*) >= 10;

The trap

WHERE COUNT(*) >= 2 is an error: the count does not exist yet when WHERE runs. Aggregates go in HAVING.

Practise filtering the groups, not the rows on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.