SQL guide · Aggregation

SQL HAVING: Filter Groups After GROUP BY

The HAVING clause filters groups after GROUP BY has formed them. It exists because WHERE cannot test aggregate values: WHERE runs on individual rows before any grouping happens, so a condition like COUNT(*) > 5 is not valid there. HAVING runs afterwards and can compare the result of COUNT, SUM, AVG, MIN or MAX against a value.

Use it to answer questions about groups rather than rows: which customers placed more than ten orders, which product categories earned over 50,000, or which departments have an average salary below a threshold. Only groups whose condition evaluates to true are kept in the result.

A good habit is to move as many conditions as possible into WHERE, and keep HAVING for conditions that truly depend on an aggregate. Filtering rows early means fewer rows have to be grouped, which is faster. For example, restricting to orders from 2026 belongs in WHERE, while requiring at least five such orders per customer belongs in HAVING.

Basic syntax

SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
[WHERE row_condition]
GROUP BY column1
HAVING AGGREGATE_FUNCTION(column2) operator value
[ORDER BY column1];

The HAVING condition can use aggregate functions and the grouping columns. It is evaluated once per group, after GROUP BY and before ORDER BY. A query without GROUP BY may still use HAVING, in which case the whole result is treated as one group.

SQL HAVING examples

Customers with many orders

The rows are grouped by customer and counted, then HAVING keeps only customers with more than five orders. Customers with five or fewer disappear from the output.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC;

WHERE and HAVING together

WHERE first limits the rows to orders placed in 2026, then the surviving rows are grouped by customer, and HAVING keeps customers whose 2026 spend exceeds 1,000.

SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spend_2026
FROM orders AS o
INNER JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.order_date >= '2026-01-01'
GROUP BY o.customer_id
HAVING SUM(oi.quantity * oi.unit_price) > 1000;

Average and a range of values

Several aggregate conditions can be combined with AND. This finds departments with at least three people whose average salary lies between 50,000 and 90,000.

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3
   AND AVG(salary) BETWEEN 50000 AND 90000;

Finding duplicate values

Grouping by a column and keeping groups with a count above one is the classic way to find duplicates. Here it lists email addresses that appear on more than one customer record.

SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Where SQL dialects differ

PostgreSQL: Does not allow output aliases in HAVING, so repeat the aggregate expression rather than writing HAVING order_count > 5.

MySQL: Is an exception and allows column aliases in HAVING, so HAVING order_count > 5 works. It also lets HAVING reference columns in the select list that are not grouped, although this is a MySQL extension that other databases reject.

SQL Server: Requires the aggregate expression in HAVING and does not accept select-list aliases there. Use a subquery or common table expression if you want to filter on an alias.

SQLite: Supports HAVING after GROUP BY as in the standard. Aliases from the select list are accepted there, but repeating the aggregate expression keeps the query portable.

Oracle: Follows the standard. Aliases from the select list cannot be used in HAVING, so repeat the expression.

Common mistakes

  • Using HAVING for row-level conditions such as HAVING country = 'US'. It may work, but filtering in WHERE is clearer and usually faster because fewer rows get grouped.
  • Using WHERE COUNT(*) > 1. This is invalid because aggregates are not available at the row-filtering stage. Move the test to HAVING.
  • Referencing a select-list alias in HAVING on a database that does not allow it. Repeat the full aggregate expression instead.
  • Forgetting that HAVING removes groups, so a customer with no qualifying orders will not appear. If you need a zero row, use a left join or conditional aggregation.
  • Counting rows after a join that multiplies them. A HAVING COUNT(*) > 1 check can pass for the wrong reason, so count distinct keys when joins are involved.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator