Level 8: Filtering groups with HAVING

Lesson

Once the bundles are formed by GROUP BY, you sometimes want to keep only some of them: the sellers whose total is over 1200, the departments with more than 2 people. This filter is about an aggregate, which only exists after grouping.

WHERE cannot do it: it acts before grouping, row by row, when the totals have not been calculated yet. WHERE SUM(amount) > 1200 therefore causes an error. HAVING is the WHERE of groups: it runs after GROUP BY and tests the aggregates.

HAVING goes right after GROUP BY: GROUP BY seller HAVING SUM(amount) > 1200. On the sales, Ana (1150) and Dan (900) are left out, Ben (1450) and Cleo (1550) remain. The tested aggregate does not need to be shown in the SELECT.

WHERE and HAVING work together: WHERE chooses the rows that go into the calculation, HAVING chooses the groups that come out. With WHERE region = 'North' … GROUP BY seller HAVING SUM(amount) > 1200, the totals are calculated only on the North sales, then only the big totals are kept.

Rule of thumb: a condition on an ordinary column goes in WHERE, a condition on an aggregate goes in HAVING.

Full order of execution: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Keep it in mind: it explains where each filter is allowed to go.

Syntax

SELECT column, AGG(x)
FROM my_table
WHERE …
GROUP BY column
HAVING AGG(x) > value;

Worked example

SELECT seller, SUM(amount) AS total
FROM sales
GROUP BY seller
HAVING SUM(amount) > 1200;

We compute each seller's total, then keep only those above 1200.

Example result

Key points

• WHERE: condition on rows. HAVING: condition on groups.
• HAVING comes after GROUP BY.
• An aggregate in a condition must go in HAVING.

Common pitfalls

• WHERE COUNT(*) > 2 causes an error: write HAVING COUNT(*) > 2.
• A condition on a column that is not grouped (GROUP BY seller HAVING month = '2025-01') is an error in standard SQL. SQLite accepts it but only tests an arbitrary row of the group: put it in WHERE. On a GROUP BY column, it is allowed in HAVING, but clearer and faster in WHERE.

The level’s 5 exercises

1. Guided · Show the departments with more than 2 employees, with their number of employees. (Table: staff)
2. Practice · Show the subjects whose average grade is at least 12.5, with that average. (Table: grades)
3. Practice · Show the id of the members who made at least 3 loans, with their number of loans. (Table: loans)
4. Practice · Counting only the flights shorter than 120 minutes, show the airlines that sold more than 300 seats, with their total. (Table: flights)
5. Challenge · Show the artists (artist_id) whose average song length is above 240 seconds, with that average, from longest to shortest. (Table: songs)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic