HAVING: filtering groups, and how it differs from WHERE
HAVING filters groups after GROUP BY, using aggregates such as COUNT(*) > 1, while WHERE filters single rows before any grouping happens.
Why it matters
"Customers with more than one order", "departments whose average salary is over 55000". You can't test those in WHERE, because the counts and averages don't exist until the rows are grouped. HAVING is the filter that runs after grouping.
The idea
Think of two sieves. WHERE is the first sieve: it removes rows before they are put into groups. HAVING is the second: it removes whole groups after they are counted.
A query can use both: WHERE keeps only delivered orders, GROUP BY makes a group per customer, then HAVING keeps customers whose delivered total is at least £100.
If a condition is about one row (status, date), put it in WHERE — it is cheaper, because fewer rows get grouped.
Picking a football team. WHERE is the trial: players under 16 are sent home before teams are formed. HAVING comes after the teams are formed: any team with fewer than 11 players is dropped from the tournament.
In football you could also drop a team for one bad player. HAVING can't look at individual rows any more — only at things that describe the whole group, such as counts, sums and the grouping columns.
Syntax
SELECT group_column, COUNT(*)
FROM table_name
WHERE row_condition
GROUP BY group_column
HAVING group_condition;How it works
- FROM: all 10 orders.
WHERE status = 'delivered': 7 rows are left.- GROUP BY customer_id: customer 1 has 4 delivered orders (275), customer 2 has 2 (275), customer 3 has 1 (60).
- HAVING
SUM(amount) >= 100: customer 3's group fails and is removed. - SELECT returns customers 1 and 2 with their totals.
Key points
- WHERE filters rows, before GROUP BY. HAVING filters groups, after GROUP BY.
- Aggregates can be used in HAVING but not in WHERE.
- A row-level condition belongs in WHERE: it is clearer and faster.
- HAVING can test an aggregate that isn't in SELECT, such as
HAVING MAX(salary) > 60000.
Common mistakes
- Writing
WHERE COUNT(*) > 1— an error, because counts don't exist yet when WHERE runs. - Putting a plain row condition in HAVING (
HAVING status = 'delivered'): it either fails or does something unexpected. - Using a SELECT alias in HAVING (
HAVING headcount > 1). Some databases allow it; standard SQL and SQL Server don't. Repeat the aggregate.
Not true: An alias from SELECT can be used in HAVING in every database.
In fact: Some databases allow it, but standard SQL and SQL Server do not. Repeat the aggregate in HAVING to be safe.
In the interview
WHERE and HAVING are both filters but run at different stages. WHERE filters individual rows before grouping, so it can't use aggregates. HAVING filters groups after GROUP BY, so it can. If a condition doesn't need an aggregate I put it in WHERE, because filtering early means less data to group.
Part of Unit 2: Summarising data: aggregates, GROUP BY and HAVING in the free SQL course.