Chapter 16 · intermediate · 5 min
HAVING: filtering groups, not rows
You can filter rows with WHERE before they're grouped. But what if the condition depends on the aggregate itself, like "only account types averaging over RM 4,000"? At the point WHERE runs, that average doesn't exist yet. HAVING is the clause built for exactly this.
The dataset
The same `accounts` table.
WHERE filters rows, before grouping
WHERE runs before GROUP BY even happens, so it can only see individual row values, never an aggregate:
| acct_type | avg_balance |
|---|---|
| current | 2816.67 |
| savings | 5366.67 |
WHERE filtering individual rows first
HAVING filters groups, after aggregating
HAVING runs after GROUP BY, so it can test the aggregate result directly. Trying to write WHERE AVG(balance) > 4000 would fail, because that average doesn't exist at the point WHERE runs; HAVING AVG(balance) > 4000 works because the groups already exist by then:
| acct_type | avg_balance |
|---|---|
| savings | 5366.67 |
Only savings survives; current's average (2816.67) doesn't clear the bar.
Only account types averaging over RM 4,000
Using both together
Nothing stops you from combining them: WHERE narrows the rows first, then HAVING narrows the resulting groups:
| acct_type | avg_balance |
|---|---|
| savings | 5366.67 |
WHERE and HAVING, doing their separate jobs
The rule that resolves the confusion
| Clause | Runs | Can test an aggregate? |
|---|---|---|
WHERE | before grouping | no, the aggregate doesn't exist yet |
HAVING | after grouping | yes, that's its whole purpose |
If you've used Excel or Google Sheets: HAVING has no clean spreadsheet equivalent, because pivot tables don't really separate "filter the source data" from "filter the summarized result" the way SQL does. You'd normally just delete rows from the pivot's output by hand. That's actually a good reason HAVING feels unfamiliar at first: it's a real SQL-specific idea, not a translation of something you already know.
Ready to practice? GXBank's average balance by type question below tests this exact WHERE-vs-HAVING distinction.
A one-line rule that resolves almost any WHERE-vs-HAVING confusion: if the condition involves an aggregate function, it belongs in HAVING; otherwise it belongs in WHERE. Next, we head back to NULL for a deeper look at COALESCE and NULLIF.