# SQL HAVING: Filter Groups After GROUP BY

- URL: https://betteranalyst.com/sql/having

SQL guide · Aggregation

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

- [SQL GROUP BY](https://betteranalyst.com/sql/group-by)
- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL SELECT](https://betteranalyst.com/sql/select)
- [SQL ORDER BY](https://betteranalyst.com/sql/order-by)
- [SQL INNER JOIN](https://betteranalyst.com/sql/inner-join)
- [SQL AND](https://betteranalyst.com/sql/and)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)