The WHERE filters the rows, and the HAVING filters the groups. That single sentence is the whole lesson, but the consequences run deep. The WHERE runs before the grouping and it can only see the individual rows, while the HAVING runs after and it can only see the 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.
The previous lesson in this module covered "How Does GROUP BY Collapse Rows Into Summaries?", and the next lesson in this module is about "How Do Multi-Column Groups Answer Layered Questions?".
When does the HAVING filter something that the WHERE cannot?
Consider the request. It is "show me the cities where the 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 the WHERE. The revenue threshold is a group-level fact, which is a sum over the six rows, and a row has no opinion about it. That is the dividing line.
What does the WHERE do before any grouping happens?
The WHERE removes the rows from the table before the engine starts forming the buckets, so it can only test the values that exist on a single row. Filtering to the completed rentals first changes the counts that 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 7Bristol drops from nine rentals to seven, because both of the excluded rows are the hires that are still out on the road. Cardiff drops from eight to five, because of the two cancellations and one still out. And Glasgow goes from seven to seven, because 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 that the engine has to read, the faster the rest of the query runs.
What does the HAVING do once the groups exist?
The HAVING is evaluated after the aggregation, so it can test a count or a sum. Here is 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.5All three of the cities clear the seven hundred pounds, so all three of them survive. The filter is still doing the work. It is simply that this dataset is not harsh enough to cut anything.
A HAVING clause is a promise about the groups, and a threshold that removes nothing is still worth writing. It documents the requirement, and it starts biting the moment that the data changes.
The 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 4Four of the bike types clear the bar, and the cargo bikes, which are the slowest and the 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 the HAVING is evaluated after the SELECT list is computed, SQLite and PostgreSQL let you test a SELECT alias directly. The condition HAVING revenue > 700 works there, where WHERE revenue > 700 would not. The 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 to let the SELECT alias be a convenience for the 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.
There is the FROM, where the rows are read. There is the WHERE, where the individual rows are filtered. There is the GROUP BY, where the surviving rows are bucketed. There is the HAVING, where the buckets are filtered. There is the SELECT, where the output columns are computed. There is the ORDER BY, where the result is sorted. And there is the LIMIT, where the result is capped.
Two consequences follow from that. First, the WHERE cannot reference a SELECT alias, because the alias does not exist yet. But the HAVING can, in most of the engines, and so can the ORDER BY. Second, the WHERE cannot contain an aggregate at all. You try to filter the groups with the 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 the 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. You move the condition into the HAVING, where the count already exists.
Quick reference
- The WHERE filters the rows before the grouping, and it can only test the single-row values.
- The HAVING filters the groups after the aggregation, and it can test the counts, the sums and the averages.
- The clause order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY and LIMIT.
- An aggregate inside the WHERE is an error and not a warning, because the groups do not exist yet.
- The HAVING can use a SELECT alias, while the WHERE usually cannot, because the alias is computed later.
Filter drill: you write one query that returns only the bike types that were rented at least four times and only counting the completed rentals. Then you write a second query that returns only those same bike types whose completed revenue exceeds five hundred pounds. You say aloud which of the two filters belongs in the WHERE and which one belongs in the HAVING.