35 HAVING

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

• 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

-- 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

To run (requires orders_example from the joins lesson):

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

Common 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.

Downloads

SQL file: having.sql · All lesson files · Setup guide