GROUP BY with HAVING
Use HAVING to keep only departments whose average salary exceeds a threshold after aggregation.
Overview
HAVING filters groups after aggregation — the group-level equivalent of
WHERE, which filters individual rows before grouping. Use it when the
condition depends on an aggregate (like "departments with average salary over
$90,000"), which WHERE cannot reference.
The Query
Employees & Departments
Loading database engine...
Step-by-Step Breakdown
GROUP BY d.name— aggregates per department.HAVING AVG(e.salary) > 90000— drops groups whose average pay is at or below $90k. This can't be aWHEREclause becauseAVGisn't known yet.WHERE(if any) would run before grouping;HAVINGruns after.
WHERE vs HAVING
WHERE e.salary > 90000 would drop low-salary employees first; HAVING AVG(e.salary) > 90000 drops low-average departments. Very different results.
Variations
Keep only categories with more than 2 orders:
Store (Customers/Orders/Products)
Loading database engine...
Common Mistakes
- Using
WHEREon an aggregate.WHERE AVG(salary) > 90000is a syntax error; aggregates belong inHAVING. - Referencing ungrouped columns in HAVING. Just like
SELECT,HAVINGexpressions must use grouped or aggregated columns. - Forgetting
GROUP BY.HAVINGwithout aGROUP BYtreats the whole table as one group.