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.
Schema
| id | INT |
| owner | TEXT |
| acct_type | TEXT |
| balance | NUMERIC |
Example data
WHERE filters rows, before grouping
WHERE runs before GROUP BY even happens, so it can only see individual row values, never an aggregate.
Before you run it — which average do you think comes out higher, savings or current?
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.
Before you run it — based on the averages from the last example, does savings or current clear the RM 4,000 bar, or both, or neither?
Using both together
Nothing stops you from combining them: WHERE narrows the rows first, then HAVING narrows the resulting groups.
Before you run it — Devi's 450.00 current-type row gets excluded by WHERE balance > 500. Does that change which acct_type survives HAVING?
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.