HAVING: Filtering Groups

HAVING filters grouped results. WHERE decides which individual rows are allowed into the grouping step; HAVING decides which summary groups survive after aggregates like COUNT(*), SUM(price), and AVG(price) have been computed.

That one distinction prevents the classic mistake: trying to use an aggregate inside WHERE.

The classic error: aggregate in WHERE

Suppose the cafe only wants categories with at least two menu items. You know WHERE filters rows, so the first attempt often looks reasonable:

SELECT category,
       COUNT(*) AS items
FROM menu_items
WHERE COUNT(*) >= 2
GROUP BY category
ORDER BY category;

ERROR:  aggregate functions are not allowed in WHERE
LINE 4: WHERE COUNT(*) >= 2
              ^

PostgreSQL is right to reject it. WHERE looks at one row at a time, before categories have been folded into groups. At that moment, there is no category count yet. COUNT(*) only exists after GROUP BY has split the rows and the aggregate has been applied inside each pile.

The fixed query moves the group-level condition to HAVING:

SELECT category,
       COUNT(*) AS items
FROM menu_items
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY category;

 category | items
----------+-------
 coffee   |     4
 pastry   |     3
 tea      |     2
(3 rows)

drinks disappeared because its group has only one row. Notice what did not happen: no individual drink row was inspected by HAVING. The drink group was built first, counted, and then removed.

WHERE first, HAVING later

Most real reports use both filters. WHERE narrows the raw rows first; GROUP BY summarizes what survived; HAVING filters the summary groups.

Ask for categories with at least two stocked items:

SELECT category,
       COUNT(*) AS stocked_items,
       SUM(price) AS stocked_total
FROM menu_items
WHERE in_stock = true
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY stocked_items DESC, category;

 category | stocked_items | stocked_total
----------+---------------+---------------
 coffee   |             3 |         10.30
 pastry   |             3 |         10.90
(2 rows)

Read it in two passes:

First, WHERE in_stock = true removes Cold Brew and Sencha before grouping, because they are sold out. Then GROUP BY category builds category piles from the stocked rows only. Then HAVING COUNT(*) >= 2 keeps only category piles with at least two stocked items.

That is why tea is gone here even though it had two items in the earlier query. After the row filter, tea has only one stocked item left.

HAVING can use any aggregate condition

HAVING is not limited to COUNT(*). Any aggregate you learned in 4.1 can define the group-level condition.

Here the report keeps categories whose total menu price is at least 6, then sorts the surviving groups by that total:

SELECT category,
       COUNT(*) AS items,
       SUM(price) AS total_price
FROM menu_items
GROUP BY category
HAVING SUM(price) >= 6
ORDER BY total_price DESC;

 category | items | total_price
----------+-------+-------------
 coffee   |     4 |       14.80
 pastry   |     3 |       10.90
 tea      |     2 |        6.40
(3 rows)

ORDER BY sorts the grouped output rows. Here it sorts the category summaries, not the original ten menu items. The alias total_price works in ORDER BY, which you saw back in 2.4; the aggregate expression SUM(price) is the value being sorted.

A useful pattern for reports

Most grouped reports you write in this section will follow this shape:

SELECT category,
       COUNT(*) AS items,
       SUM(price) AS total_price,
       AVG(price) AS average_price
FROM menu_items
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY total_price DESC;

 category | items | total_price |   average_price
----------+-------+-------------+--------------------
 coffee   |     4 |       14.80 | 3.7000000000000000
 pastry   |     3 |       10.90 | 3.6333333333333333
 tea      |     2 |        6.40 | 3.2000000000000000
(3 rows)

Choose the grain with GROUP BY. Compute the aggregate columns. Keep only the groups that match the business rule with HAVING. Sort the final summary rows with ORDER BY.

Try changing the threshold in the playground: COUNT(*) >= 3, SUM(price) >= 11, or AVG(price) >= 3.5. The row-count footer tells you how many groups survived.

You now have the local rule: WHERE filters rows before grouping, and HAVING filters groups after aggregation. Next lesson zooms out to the full logical order of a query, the map that explains alias scope, filtering, grouping, sorting, and limits in one pass.