SQL Reference ยท Statements and Clauses
SQL HAVING clause
Reference for the SQL HAVING clause: filter groups by aggregates such as COUNT and SUM, how HAVING differs from WHERE, and dialect notes.
HAVING filters groups. After GROUP BY has collapsed rows into groups and the aggregates are computed, HAVING keeps only the groups whose condition is true. It is the only clause where you can test an aggregate such as COUNT(*) or SUM(price).
The rule of thumb: WHERE filters rows before grouping; HAVING filters groups after. A condition that needs no aggregate belongs in WHERE, where it is cheaper.
SELECT category_id, COUNT(*) AS products FROM products GROUP BY category_id HAVING COUNT(*) >= 5;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SELECT grouping_expr, aggregate(...) FROM table [ WHERE row_condition ] GROUP BY grouping_expr HAVING group_condition;
| Part | Description |
|---|---|
group_condition |
A boolean expression over grouping columns and aggregate functions. |
Without GROUP BY |
The whole table is one group, so HAVING COUNT(*) > 0 returns either one row or none. |
Returns: the groups for which the condition is true, with the select list evaluated per group.
Dialect differences
- Select aliases in
HAVING: SQLite and MySQL accept them; PostgreSQL, SQL Server and Oracle do not. Portable code repeats the aggregate:HAVING COUNT(*) >= 5. HAVINGwithoutGROUP BYis standard, and works in all five dialects covered here.- SQLite historically accepted
HAVINGonly withGROUP BY; since 3.39 it follows the standard.
-- MySQL and SQLite accept an alias; SQL Server and Oracle do not SELECT category_id, COUNT(*) AS n FROM products GROUP BY category_id HAVING n >= 5;
Usage notes
- Aggregates are not allowed in
WHERE.WHERE COUNT(*) > 5is an error; move it toHAVING. - Filter rows in
WHEREwhen you can.HAVING department_id = 3works but groups every department first.WHERE department_id = 3groups only the rows needed. - NULL: if the aggregate is NULL (for example
AVGof an all-NULL group), the condition is unknown and the group is dropped. - Multiple conditions combine with
ANDandORlike inWHERE. - Subqueries are allowed in
HAVING, for example comparing a group's average with the overall average. - Column order in the result is the select list, not the
HAVINGclause; you do not have to select what you filter on.