Aggregation
Filtering groups with HAVING
In the previous chapter, you grouped orders by status:
123456SELECT status, COUNT(*) AS order_countFROM ordersGROUP BY statusORDER BY order_count DESC;You should see:
1234567 status | order_count------------+------------- delivered | 70000 shipped | 15000 processing | 8000 cancelled | 5000 pending | 2000Now imagine we want only statuses that have more than 10000 orders.
We need to filter based on:
1COUNT(*)This is what HAVING is for.
Why WHERE doesn't work
You might try:
123456SELECT status, COUNT(*) AS order_countFROM ordersWHERE COUNT(*) > 10000GROUP BY status;PostgreSQL rejects this:
1ERROR: aggregate functions are not allowed in WHEREThe reason is that WHERE filters individual rows before grouping and aggregation happen.
At the WHERE stage:
1COUNT(*)doesn't exist yet.
Conceptually:
1234567FROM ↓WHERE ↓GROUP BY ↓aggregate functionsSo WHERE cannot use an aggregate result to decide which rows to keep.
HAVING
To filter groups after aggregation, use:
1HAVINGRun:
1234567SELECT status, COUNT(*) AS order_countFROM ordersGROUP BY statusHAVING COUNT(*) > 10000ORDER BY order_count DESC;You should see:
1234 status | order_count-----------+------------- delivered | 70000 shipped | 15000PostgreSQL first creates the status groups.
Then it calculates their counts.
Then:
1HAVING COUNT(*) > 10000keeps only groups whose count is greater than 10000.
1234567891011121314GROUP BY statusdelivered → 70000shipped → 15000processing → 8000cancelled → 5000pending → 2000 │ ▼ HAVING COUNT(*) > 10000 │ ▼delivered → 70000shipped → 15000WHERE versus HAVING
The most useful distinction is:
12345WHERE→ filters rowsHAVING→ filters groupsFor example:
1WHERE status <> 'delivered'removes individual delivered order rows before grouping.
While:
1HAVING COUNT(*) > 5000removes groups after their counts have been calculated.
Using WHERE and HAVING together
The two clauses can appear in the same query.
Run:
12345678SELECT status, COUNT(*) AS order_countFROM ordersWHERE status <> 'delivered'GROUP BY statusHAVING COUNT(*) > 5000ORDER BY order_count DESC;The logical processing is:
1234567891011121314151617181920212223242526orders │ ▼WHERE status <> 'delivered' │ ▼shippedprocessingcancelledpending │ ▼GROUP BY status │ ▼shipped → 15000processing → 8000cancelled → 5000pending → 2000 │ ▼HAVING COUNT(*) > 5000 │ ▼shipped → 15000processing → 8000You should see:
1234 status | order_count------------+------------- shipped | 15000 processing | 8000Notice that:
1cancelled = 5000does not pass:
1> 5000because 5000 is not greater than 5000.
Logical processing order
For the concepts we have covered so far, a useful logical model is:
12345678910111213FROM ↓WHERE ↓GROUP BY ↓aggregate calculations ↓HAVING ↓SELECT ↓ORDER BYThis is a logical processing model for understanding the query.
It does not mean PostgreSQL's query planner must physically execute every operation in exactly that order.
The planner is free to choose an efficient execution strategy as long as the query produces the required result.
HAVING with other aggregate functions
HAVING is not limited to COUNT().
For example, suppose we want product categories whose average price is above 30:
1234567SELECT category, ROUND(AVG(price), 2) AS average_priceFROM productsGROUP BY categoryHAVING AVG(price) > 30ORDER BY average_price DESC;You should see:
12345 category | average_price----------+--------------- audio | 30.03 garden | 30.02 office | 30.01The two remaining categories, outdoor and home, average 30.00 and 29.99, so they are removed.
The aggregate used in HAVING determines which groups remain.
You can use aggregates such as:
12345COUNT()SUM()AVG()MIN()MAX()depending on the condition you need.
The HAVING aggregate does not have to be selected
Consider:
12345SELECT statusFROM ordersGROUP BY statusHAVING COUNT(*) > 10000;This is valid, and returns:
1234 status----------- delivered shippedCOUNT(*) is used to decide which groups survive, even though the count is not returned in the SELECT list.
So:
12345SELECT→ what values should be returnedHAVING→ which groups should remainare separate decisions.
Conditions on grouping columns
You could write:
123456SELECT status, COUNT(*)FROM ordersGROUP BY statusHAVING status <> 'delivered';This is valid because status is the grouping column.
However, this condition does not depend on an aggregate.
It can be applied before grouping:
123456SELECT status, COUNT(*)FROM ordersWHERE status <> 'delivered'GROUP BY status;The second version usually expresses the intent more clearly:
12remove unwanted rows firstthen group what remainsA useful rule is:
If a condition can be applied to individual rows before grouping, prefer `WHERE`. Use `HAVING` when the condition depends on the grouped or aggregated result.
HAVING without GROUP BY
PostgreSQL also allows HAVING without an explicit GROUP BY.
For example:
123SELECT COUNT(*) AS order_countFROM ordersHAVING COUNT(*) > 50000;Because there is an aggregate but no GROUP BY, all selected rows are treated as one group.
The condition is true, so the query returns the group:
123 order_count------------- 100000Change the threshold so the condition is false:
123SELECT COUNT(*) AS order_countFROM ordersHAVING COUNT(*) > 500000;and the query returns no row at all, not a row containing 0.
This is valid SQL, but HAVING is most commonly encountered together with GROUP BY.
The core mental model
When reading an aggregation query, ask two different questions:
12345WHERE→ Which rows should participate?HAVING→ Which resulting groups should remain?The important point is:
`WHERE` filters rows before grouping, while `HAVING` filters groups after aggregation.