Lesson 24 / 37 · Grouping

WHERE filters rows, HAVING filters groups

WHERE runs before grouping and sees single orders. HAVING runs after and sees totals per country, which is why it can use sum(). Order 107 (5.00) stops at WHERE; the GB group stops at HAVING.

The query

SELECT c.country, count(*) AS orders, sum(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid' AND o.amount > 10
GROUP BY c.country
HAVING sum(o.amount) > 100

Try moving o.amount > 10 into HAVING: the query fails, because after grouping a country has no single o.amount to test.

Next: Finding duplicates with GROUP BY and HAVING · All lessons