HAVING Clause for Filtering Groups
What You'll Learn
You'll learn how to filter entire groups of data after aggregation, something WHERE cannot do.
The Problem WHERE Can't Solve
You've learned to use WHERE to filter individual rows before grouping, and GROUP BY to organize data into groups for aggregation. But what if you want to filter groups themselves based on aggregate results?
For example: "Show me only departments where the average salary exceeds $50,000" or "Find categories with more than 10 products."
WHERE filters rows before grouping happens. HAVING filters groups after aggregation.
Understanding HAVING
The HAVING clause works like a WHERE for groups. It examines the result of aggregate functions (COUNT, SUM, AVG, etc.) and decides which groups make the final cut.
Analogy: Imagine sorting students into study groups (GROUP BY), calculating each group's average test score (aggregate function), then only keeping groups with an average above 80% (HAVING). You can't decide which groups qualify until after you've calculated their averages.
Syntax Pattern
SELECT column, AGGREGATE_FUNCTION(column)
FROM table
WHERE condition_on_rows
GROUP BY column
HAVING condition_on_aggregates
ORDER BY column;
Example
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
This groups employees by department, calculates average salaries, then filters out any departments where the average is 50,000 or below.
When to Use What
- WHERE: Filter individual rows before grouping
- HAVING: Filter groups based on aggregate calculations
Key Takeaway: Use HAVING to filter groups based on aggregate conditions—it evaluates after GROUP BY creates the groups, unlike WHERE which filters raw rows beforehand.