Lesson 9 of 20

In progress

Course progress

SQL

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 WHERE and HAVING.
  • Combine both to keep groups with zero matches visible.

The basic form

SQL
Editable. Runs in your browser.





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

  • WHERE filters rows; it cannot use COUNT, SUM or any aggregate.
  • HAVING filters 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.

SQL
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

SQL
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

SQL
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

SQL
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:

SQL
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

SQL
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

SQL
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
SQL
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

  • HAVING filters groups after aggregation and is the only place for aggregate conditions.
  • Put row-level conditions in WHERE so the aggregation processes fewer rows.
  • WHERE removes 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?
It can reference aggregates, and in lenient databases any column, but only grouped columns are meaningful.
Does HAVING run before ORDER BY?
Yes. Groups are filtered, then the remaining rows are sorted.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.