HAVING

Explanation

WHERE filters rows before they are grouped. But what if you want to keep only the groups that meet a condition — for example, categories that contain more than three products?

That is exactly what HAVING is for. It filters groups after GROUP BY has done its work, so it can test aggregate values like COUNT(*) or SUM(price).

The mental model:

1. WHERE removes rows you don't want.
2. GROUP BY groups what remains.
3. Aggregates compute one value per group.
4. HAVING keeps only the groups that pass its test.

Syntax

SELECT column, AGGREGATE(col)
FROM table_name
GROUP BY column
HAVING AGGREGATE(col) > some_value;

Interactive Example

Show only categories that have more than three products. Then show categories whose average price is above 50.

Common Mistakes

• Using WHERE to test an aggregate. WHERE COUNT(*) > 3 is invalid — aggregates aren't available yet. Use HAVING.
• Forgetting the GROUP BY. HAVING without grouping usually makes no sense; it filters the single whole-table group.
• Confusing the two filter points. Remember: WHERE → rows in, HAVING → groups out.

Best Practices

• Apply row-level filters in WHERE first (it's cheaper), then group, then use HAVING only for aggregate conditions.
• You can reuse the same aggregate in HAVING that you used in SELECT.
• Aliases defined in SELECT are not portable to HAVING in every database, so repeating the aggregate expression is the safest habit.

Practice Question

Using products, return each category with its total stock, but only for categories whose total stock is greater than 400.

Summary

HAVING filters grouped rows using aggregate conditions that WHERE cannot express. The pipeline is WHERE → GROUP BY → aggregates → HAVING → ORDER BY.

FAQ