# HAVING

> Filter aggregate groups with HAVING and distinguish group predicates from row filters.

---

LLMS index: [llms.txt](/en/llms.txt)

---

`WHERE` filters individual rows *before* grouping; `HAVING` filters *groups* after the aggregates have been computed. If you want "customers with more than three orders," that is a `HAVING` — you cannot test `count(*) > 3` until the count exists. If you want "customers who placed an order in 2024," that is a `WHERE` — the year is a per-row property.

## What you'll learn {#learn}

- Filtering groups with `HAVING`
- The difference between `WHERE` (per row) and `HAVING` (per group)
- Combining `WHERE` and `HAVING` in one query
- Aggregates allowed inside `HAVING`
- Why pushing predicates into `WHERE` is usually faster

## Example {#example}

```sql {num="35.1" caption="HAVING"}
-- HAVING with one aggregate condition
SELECT customer_id, count(*) AS order_count, sum(total) AS total_spent
FROM orders_example
GROUP BY customer_id
HAVING sum(total) > 30
ORDER BY customer_id;

-- HAVING with multiple conditions
SELECT customer_id, count(*) AS order_count, sum(total) AS total_spent
FROM orders_example
GROUP BY customer_id
HAVING count(*) >= 1 AND sum(total) > 30;

-- WHERE then HAVING: filter rows first, group, then filter groups
SELECT customer_id, count(*) AS order_count
FROM orders_example
WHERE total > 20                      -- per-row filter
GROUP BY customer_id
HAVING count(*) >= 1;                 -- per-group filter

-- HAVING without aggregates is legal but pointless — same as WHERE
SELECT customer_id
FROM orders_example
GROUP BY customer_id
HAVING customer_id > 50;              -- could just be WHERE
```

The execution order helps remember the difference: `FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY`. A row predicate written in `WHERE` makes its intent clear and can reduce the input to grouping; PostgreSQL can also push suitable `HAVING` predicates down. Push as much filtering as possible into `WHERE`.

`HAVING` lets you write aggregate predicates directly: `HAVING avg(amount) > 100`, `HAVING count(DISTINCT product_id) > 5`. These cannot appear in `WHERE` because the aggregates do not exist there.

The last query is technically valid but a smell: filtering on a non-aggregate in `HAVING` does the same as `WHERE` but later in the pipeline. Prefer `WHERE` for clarity; the planner can often push this grouped-column predicate down either way.

## Run the example {#run}

To run (requires `orders_example` from the joins lesson):

```text
$ psql -f source/having.sql postgres
 customer_id | order_count | total_spent
-------------+-------------+-------------
           1 |           2 |       80.00
(1 row)
 ...
```

## Common pitfalls {#pitfalls}

- Writing `WHERE count(*) > 3` — aggregates are not allowed in `WHERE`. Use `HAVING`.
- Writing `HAVING amount > 100` is valid only when `amount` is grouped or functionally dependent on the grouping. Use `WHERE amount > 100` to filter individual input rows. The planner usually rewrites this, but readers will be confused.
- Forgetting that `HAVING` runs after `GROUP BY`, so any column referenced must be a `GROUP BY` column or inside an aggregate.
- Using `HAVING` instead of a `WHERE` in `count(*) FILTER (WHERE ...)`-style logic. `FILTER` is per-aggregate and lives in `SELECT`; `HAVING` is per-group and decides which groups are kept.

> [!TIP] Tip
> When you want "the top N groups by some aggregate," combine `GROUP BY` + `ORDER BY agg DESC` + `LIMIT N` — no `HAVING` needed. `HAVING` is for predicates ("at least 3"), not rankings ("top 3").

> [!NOTE] Try it
> Change the threshold: `HAVING sum(total) < 50` and see how the result set shifts. Then move the filter into `WHERE`: `WHERE total > 20 GROUP BY customer_id;` and observe that the count drops because fewer rows reach the grouping.

## Downloads {#downloads}

SQL file: [having.sql](/examples/source/having.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)