Lesson 9 of 20
In progress
HAVING: filtering groups
Filter after aggregation, tell HAVING from WHERE, and combine both without hiding groups that should appear with zero.
WHERE cannot see aggregates, because it runs before the groups exist. HAVING runs after, and that is the whole reason it exists.
What you will learn
- Filter groups by an aggregate.
- Choose correctly between
WHEREandHAVING. - Combine both to keep groups with zero matches visible.
The basic form
Editable. Runs in your browser.
INSERT INTO readings (id, sensor, celsius, taken_on) VALUES
(1, 'a1', 21.5, '2025-05-01'),
(2, 'a1', 22.0, '2025-05-02'),
(3, 'a1', 25.5, '2025-05-03'),
(4, 'b2', 18.0, '2025-05-01'),
(5, 'b2', 19.5, '2025-05-02'),
(6, 'c3', 30.0, '2025-05-03');
SELECT sensor, ROUND(AVG(celsius), 1) AS avg_c
FROM readings
GROUP BY sensor
HAVING AVG(celsius) > 21;
Press Run to see the output.
Two sensors remain: a1 at 23.0 and c3 at 30.0. b2 averaged 18.75 and was removed after aggregation.
The filter can reference the aggregate directly, and it can repeat it. Aliases in HAVING work in SQLite and MySQL but not in PostgreSQL, so writing the aggregate again is the portable form.
WHERE versus HAVING
WHEREfilters rows; it cannot useCOUNT,SUMor any aggregate.HAVINGfilters groups; it can use aggregates and grouped columns.
The performance difference follows from that: WHERE reduces the rows an aggregate must process, so a condition that can be expressed on rows belongs in WHERE.
Editable. Runs in your browser.
Press Run to see the output.
This counts only hot readings per sensor. Putting the same condition in HAVING would count all readings and then filter, which is slower and, more importantly, answers a different question.
Both clauses together
Editable. Runs in your browser.
Press Run to see the output.
Read it as: keep readings from 2 May onward, group by sensor, keep sensors with at least two readings, sort by average.
Clause order in writing is fixed — WHERE, GROUP BY, HAVING, ORDER BY — and the result follows that pipeline.
HAVING with several conditions
Editable. Runs in your browser.
Press Run to see the output.
AND and OR work as in WHERE. Parentheses still matter when mixing them.
The missing-groups problem
Editable. Runs in your browser.
Press Run to see the output.
Only c3 appears. Sensors with no hot readings are absent, which a chart may render as a gap rather than a zero. If the chart should show zeros, the filter has to move:
Editable. Runs in your browser.
Press Run to see the output.
Now every sensor appears, and the count is zero where nothing matched. The lesson generalises: filtering rows changes the population; aggregating a condition keeps the population.
HAVING can remove groups, but it cannot create them. If a group should exist with no matching rows, the rows must come from a LEFT JOIN against the list of sensors.
HAVING without GROUP BY
Editable. Runs in your browser.
Press Run to see the output.
Without GROUP BY, the whole table is one group, so HAVING tests that single group. The query returns one row or none. It is valid and rarely clearer than WHERE plus inspection.
Reading the pipeline
It helps to read a grouped query as four separate steps rather than one sentence. First FROM produces rows. Then WHERE removes rows. Then GROUP BY collapses what remains into groups, and the aggregates are computed per group. Finally HAVING removes whole groups and ORDER BY sorts the survivors.
Each step can only see what the previous one produced, which explains most of the rules in this lesson: WHERE cannot use an aggregate because no aggregate exists yet, HAVING can, and an alias from SELECT is unavailable to WHERE but available to ORDER BY. Once the pipeline is clear, clause placement stops being memorisation and becomes prediction.
A real use: duplicate detection
Editable. Runs in your browser.
Press Run to see the output.
Groups with more than one row reveal duplicates. Add the HAVING condition to any grouped query when "more than one" is the interesting case — it is the fastest way to find a broken import.
Putting a row-level condition in HAVING, such as HAVING celsius > 21 next to GROUP BY sensor. SQLite may accept it and pick an arbitrary row’s value; the query is not portable and not meaningful.
Find sensors whose average is above 20 and that have at least two readings taken on or after 2025-05-02. Order by average descending.
Show solution
Editable. Runs in your browser.
Press Run to see the output.
a1 qualifies with two readings averaging 23.8; b2 has one reading and c3 has one, so both are excluded. The date filter is row-level and belongs in WHERE; the count and average conditions belong in HAVING.
Recap
HAVINGfilters groups after aggregation and is the only place for aggregate conditions.- Put row-level conditions in
WHEREso the aggregation processes fewer rows. WHEREremoves groups that would have been empty; aggregate a condition when zeros must remain visible.
Next, you will combine these pieces into the reporting patterns that real dashboards use.
Can HAVING reference a column not in GROUP BY?
Does HAVING run before ORDER BY?
Progress is saved in this browser only. No account, no email.