HAVING: Filtering the Groups Themselves

lesson 4-3 · ~9 min · 10/29

WHERE filters rows, HAVING filters buckets

Suppose you want only the customers who spent more than $10. You cannot write WHERE SUM(price) > 10: WHERE runs before grouping, when no sums exist yet. Filtering on an aggregate needs HAVING, which runs after the buckets are built:

SELECT customer, SUM(price) AS total
FROM orders
GROUP BY customer
HAVING SUM(price) > 10
ORDER BY customer;

Two new things here:

AS total gives the computed column a readable name (an alias).
The full clause order is fixed: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.

Rule of thumb: a test about a single row (its price, its item) belongs in WHERE. A test about a whole group (its total, its count) belongs in HAVING.

Code exercise · sql

Run it. Cara spent only 5, so her bucket is dropped by HAVING and two rows remain.

Quiz

You want the number of coffees each customer bought, counting only coffee orders. Where does `item = 'coffee'` belong?

Code exercise · sql

Your turn. Which items were ordered at least twice? Print the item and how many times it was ordered, alphabetically. Bagel (ordered once) should not appear.

Problem

Clause order check: put these in the order SQL requires them to appear in one query: HAVING, WHERE, GROUP BY, ORDER BY. Answer with the four words separated by spaces.