HAVING

HAVING filters the groups produced by a GROUP BY clause, based on the result of an aggregate function. It plays the same role for grouped results that WHERE plays for individual rows — the difference is entirely about when the filter is applied.

WHERE filters before grouping, HAVING filters after

Clause	Filters	Runs
`WHERE`	Individual rows	Before grouping and aggregation
`HAVING`	Groups (via an aggregate condition)	After grouping and aggregation

Because WHERE runs before the rows have even been grouped, it has no access to a group's aggregate value yet — COUNT(*), SUM(total), and similar expressions simply don't exist at that stage of execution. HAVING runs after aggregation, so it can reference those aggregate results directly.

A side-by-side example

Suppose you want to find customers who have placed more than 5 orders. It's tempting to reach for WHERE:

This fails

SQL

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

-- ERROR:  aggregate functions are not allowed in WHERE clause

PostgreSQL rejects this outright: at the point WHERE is evaluated, rows haven't been grouped yet, so there is no COUNT(*) to compare against. Swap in HAVING and it works exactly as intended:

This works

SQL

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

customer_id | order_count
------------+-------------
         42 |           9
        108 |           6
        215 |          14

Combining WHERE and HAVING

WHERE and HAVING are not mutually exclusive — a single query commonly uses both, each doing the job it is suited for. Filter out rows you never want considered at all with WHERE, then filter the resulting groups by their aggregate values with HAVING.

orders

SQL

SELECT customer_id, COUNT(*) AS delivered_orders, SUM(total) AS revenue
FROM orders
WHERE status = 'delivered'        -- filter rows first
GROUP BY customer_id
HAVING SUM(total) > 500           -- then filter groups
ORDER BY revenue DESC;

Here WHERE status = 'delivered' throws out cancelled and pending orders before any grouping happens, and HAVING SUM(total) > 500 then keeps only the customers whose delivered-order revenue passes that threshold.

HAVING can reference columns not in the SELECT list

SQL

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING AVG(amount) > 100 AND COUNT(*) >= 10;

Note

HAVING can use any aggregate expression, including ones that never appear in the final SELECT list — as shown above, filtering on AVG(amount) and COUNT(*) while only selecting SUM(amount).

Tip

A useful mental shortcut: if the condition mentions a raw column (status = 'delivered'), it belongs in WHERE. If it mentions an aggregate function (COUNT(*), SUM(total)), it belongs in HAVING.

WHERE filters individual rows before grouping; it cannot reference aggregate results.
HAVING filters groups after aggregation; it can reference aggregate results.
WHERE COUNT(*) > 5 is a compile error; HAVING COUNT(*) > 5 is correct.
A single query can use both — WHERE to trim rows, HAVING to trim groups.