Postgres by Example: 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

-- 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. Anything you can express in WHERE is cheaper than HAVING because it reduces the number of rows that have to be grouped. 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. Move it to WHERE so the planner can use indexes.

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 when amount is a row-level column — it works but should be WHERE amount > 100. 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: 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").

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.

Source: having.sql

Next: Scalar and IN Subqueries

Home: Postgres by Example