WHERE filters rows; HAVING filters groups. That single sentence is the whole lesson, but the consequences run deep: WHERE runs before the grouping and can only see individual rows, while HAVING runs after and can only see aggregate results. Any question of the form "show me the groups whose total is above a threshold" is a HAVING question, and no amount of WHERE can answer it.
When does HAVING filter something WHERE cannot?
Consider the request: "show me the cities where completed rentals brought in more than seven hundred pounds." Every clause of that sentence has to be evaluated in the right order. The status filter is a row-level fact, so it belongs in WHERE. The revenue threshold is a group-level fact — a sum over six rows — and a row has no opinion about it. That is the dividing line.
What does WHERE do before any grouping happens?
WHERE removes rows from the table before the engine starts forming buckets, so it can only test values that exist on a single row. Filtering to completed rentals first changes the counts every city ends up with:
SELECT city, COUNT(*) AS rentals
FROM rentals
WHERE status = 'completed'
GROUP BY city
ORDER BY city;
city rentals
------- -------
Bristol 7
Cardiff 5
Glasgow 7
Bristol drops from nine rentals to seven (both excluded rows are hires still out on the road), Cardiff from eight to five (two cancellations and one still out), and Glasgow from seven to seven — it had nothing to exclude. The filtered rows never reach the aggregate, so they never influence the count. This is the cheapest place to filter: the fewer rows the engine has to read, the faster the rest of the query runs.
What does HAVING do once the groups exist?
HAVING is evaluated after the aggregation, so it can test a count or a sum. The same request, completed with the threshold:
SELECT city, COUNT(*) AS rentals,
ROUND(SUM(rental_days * daily_rate), 2) AS revenue
FROM rentals
WHERE status = 'completed'
GROUP BY city
HAVING SUM(rental_days * daily_rate) > 700
ORDER BY revenue DESC;
city rentals revenue
------- ------- --------
Cardiff 5 1155.0
Glasgow 7 1130.0
Bristol 7 921.5
All three cities clear seven hundred pounds, so all three survive. The filter is still doing work — it is simply that this dataset is not harsh enough to cut anything. A HAVING clause is a promise about groups, and a threshold that removes nothing is still worth writing, because it documents the requirement and starts biting the moment the data changes.
HAVING can also test a count, which is the classic "show me the buckets with at least four rentals" filter:
SELECT bike_type, COUNT(*) AS rentals
FROM rentals
GROUP BY bike_type
HAVING COUNT(*) >= 4
ORDER BY rentals DESC, bike_type;
bike_type rentals
--------- -------
e-bike 6
hybrid 6
road 5
cargo 4
Four bike types clear the bar, and the cargo bikes — the slowest and most expensive to move around a city — happen to be the smallest category. A WHERE clause could not have produced that list, because no single row knows how many cargo bikes exist.
One more habit belongs here. Because HAVING is evaluated after the SELECT list is computed, SQLite and PostgreSQL let you test a SELECT alias directly — so HAVING revenue > 700 works there where WHERE revenue > 700 would not. Other engines are stricter about reusing an alias in a filter, so the portable spelling is to write HAVING SUM(rental_days * daily_rate) > 700 and let the SELECT alias be a convenience for readers only.
Where exactly do the two clauses sit?
The evaluation order of a query is fixed, and it explains every surprise in this area:
- FROM — the rows are read.
- WHERE — individual rows are filtered.
- GROUP BY — the surviving rows are bucketed.
- HAVING — the buckets are filtered.
- SELECT — the output columns are computed.
- ORDER BY — the result is sorted.
- LIMIT — the result is capped.
Two consequences follow. First, WHERE cannot reference a SELECT alias, because the alias does not exist yet — but HAVING can, in most engines, and so can ORDER BY. Second, WHERE cannot contain an aggregate at all. Try to filter groups with WHERE and the engine refuses outright:
SELECT city, COUNT(*) AS rentals FROM rentals WHERE COUNT(*) > 3 GROUP BY city;
Parse error near line 29: misuse of aggregate: COUNT()
The engine names the offending aggregate in the message, which is helpful, but the reasoning behind it is worth keeping: at the point WHERE runs, the groups do not exist yet, so there is nothing for an aggregate to have counted. The fix is not a workaround but a reordering — move the condition into HAVING, where the count already exists.
Quick reference
- WHERE filters rows before grouping; it can only test single-row values.
- HAVING filters groups after aggregation; it can test counts, sums and averages.
- The clause order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
- An aggregate inside WHERE is an error, not a warning — the groups do not exist yet.
- HAVING can use a SELECT alias; WHERE usually cannot, because the alias is computed later.
Filter drill: write one query that returns only bike types rented at least four times and only counting completed rentals, then a second query that returns only those same bike types whose completed revenue exceeds five hundred pounds — and say aloud which of the two filters belongs in WHERE and which in HAVING.