HAVING, FILTER and conditional aggregation

HAVING filters groups

Customers with at least two orders:

SELECT customer_id, count(*) AS orders
FROM orders
GROUP BY customer_id
HAVING count(*) >= 2
ORDER BY customer_id;

 customer_id | orders
-------------+--------
           1 |      3
           2 |      2
           3 |      2
           4 |      2
           6 |      2

The condition is about the group, so it goes in HAVING. Putting it in WHERE is an error:

SELECT customer_id, count(*) FROM orders WHERE count(*) >= 2 GROUP BY customer_id;

ERROR:  aggregate functions are not allowed in WHERE

WHERE runs before the groups exist, so there is no count to compare yet.

	WHERE	HAVING
Runs	Before grouping	After grouping
Filters	Rows	Groups
Can use aggregates	No	Yes

Use both together when you need both: WHERE status <> 'cancelled' decides which orders are counted, and HAVING count(*) >= 2 decides which customers are shown.

FILTER: aggregate only some rows

Often you want several counts from the same group: all orders, shipped orders, open orders. Add FILTER (WHERE …) to an aggregate and it only sees the rows that pass:

SELECT customer_id,
       count(*)                                           AS orders,
       count(*) FILTER (WHERE status = 'shipped')         AS shipped,
       count(*) FILTER (WHERE status IN ('pending', 'paid')) AS open
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

 customer_id | orders | shipped | open
-------------+--------+---------+------
           1 |      3 |       2 |    1
           2 |      2 |       0 |    2
           3 |      2 |       1 |    0
           4 |      2 |       1 |    1
           6 |      2 |       1 |    1
           7 |      1 |       1 |    0

One pass over the table, one row per customer, three numbers. This is called conditional aggregation, and it is how you turn rows into a side-by-side summary (sometimes called a pivot).

You will also see it written with CASE, which works in databases without FILTER:

sum(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped

Check yourself

1. When does a condition belong in HAVING rather than WHERE?

   Show answer

   When it tests an aggregate or the group as a whole, such as count(*) >= 2. WHERE runs before grouping and cannot use aggregates.

   I could explain this without looking

2. Write the aggregate that counts only cancelled orders in each group.

   Show answer

   count(*) FILTER (WHERE status = 'cancelled'), or sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END).

   I could explain this without looking

3. A query has WHERE status <> 'cancelled' and also count(*) FILTER (WHERE status = 'cancelled'). What does the filtered count return?

   Show answer

   0 for every group. WHERE already removed the cancelled rows before grouping, so the filtered aggregate never sees any.

   I could explain this without looking