Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT category_id, COUNT(*) AS products
FROM products
GROUP BY category_id
HAVING COUNT(*) >= 5;

Try it

SQL Keep only big groups ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL WHERE before grouping, HAVING after ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
  • HAVING without GROUP BY is standard, and works in all five dialects covered here.
  • SQLite historically accepted HAVING only with GROUP BY; since 3.39 it follows the standard.
SQL
-- 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(*) > 5 is an error; move it to HAVING.
  • Filter rows in WHERE when you can. HAVING department_id = 3 works but groups every department first. WHERE department_id = 3 groups only the rows needed.
  • NULL: if the aggregate is NULL (for example AVG of an all-NULL group), the condition is unknown and the group is dropped.
  • Multiple conditions combine with AND and OR like in WHERE.
  • 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 HAVING clause; you do not have to select what you filter on.