Filtering Groups with HAVING

GROUP BY lets you create one summary row per group. The next question is: how do you keep only the groups whose summary meets a condition?

For example:

• Departments with more than 2 employees
• Products with total sales over 100
• Customers whose average order is at least 25

These are not row-by-row conditions. They are conditions about groups after aggregation. That is what HAVING is for.

WHERE filters rows before groups exist

WHERE runs before grouping. At that moment, there are only individual rows. There are no group totals yet, so WHERE COUNT(*) > 2 does not make sense.

Think of it like making piles of receipts:

1. WHERE decides which receipts are allowed into the piles.
2. GROUP BY makes the piles.
3. Aggregates like COUNT and SUM summarize each pile.
4. HAVING decides which completed piles stay in the result.

HAVING filters groups

HAVING runs after GROUP BY, so it can use aggregate functions such as COUNT(*), SUM(amount), and AVG(salary).

Only Engineering survives because it has 3 employees. Sales has 2 and Marketing has 1, so those groups are filtered out.

WHERE and HAVING together

WHERE and HAVING are not competitors. They do different jobs at different moments.

Use both when you need both jobs.

Read it in stages:

1. WHERE is_intern = 0 keeps only non-intern rows.
2. GROUP BY dept makes department groups.
3. AVG(salary) calculates each group's average.
4. HAVING AVG(salary) > 80000 keeps only groups with a high average.

Filtering by totals

HAVING is also common with SUM. This query keeps only products whose total sales are at least 30.

The SUM(amount) value is created per product group, so the condition belongs in HAVING.

Filtering rows first can change group results

Because WHERE happens before grouping, it can change the totals that HAVING sees.

This query first keeps only online sales rows. Then it groups and totals those online rows. Finally, HAVING keeps products with at least 20 in online sales.

The clause order so far

The written order of a grouped query is fixed:

SELECT columns_and_aggregates
FROM table_name
WHERE row_condition
GROUP BY group_columns
HAVING group_condition
ORDER BY sort_columns;

The logical flow is:

The most important part for this page is:

WHERE happens before GROUP BY; HAVING happens after GROUP BY.

HAVING without showing the aggregate condition

The aggregate in HAVING does not have to appear in the final output, though showing it is often clearer for beginners.

This returns customers who have at least two orders, even though the count is not displayed.

Check your understanding