The GROUP BY lesson's Try It Yourself returned every department's headcount, including ones with very few employees. A natural follow-up question, departments with more than 2 employees, can't be answered with WHERE, WHERE only ever sees individual rows, before GROUP BY has even collapsed them. HAVING is the clause built specifically to filter on a group's aggregate result.
Learning Objectives
- Use HAVING to filter groups by an aggregate condition.
- State precisely why WHERE can't do what HAVING does.
- Combine WHERE, GROUP BY, and HAVING correctly in the same query.
All Rows
Filtered first by WHERE, if present
GROUP BY
Collapsed into groups
HAVING
Filters groups by their aggregate result
Final Groups
Only the groups that qualify
Basic HAVING
GROUP BY department_id forms one group per department first, then HAVING COUNT(*) > 2 keeps only the groups whose headcount is over 2, discarding smaller departments from the result entirely. COUNT(*) is recomputed inside HAVING, not reused from SELECT, HAVING always works directly with aggregate expressions.
SELECT department_id, COUNT(*) AS headcount FROM employees GROUP BY department_id HAVING COUNT(*) > 2;
WHERE and HAVING in the Same Query
WHERE filters individual rows first (only employees hired from 2020 onward), then GROUP BY collapses whatever rows survived, then HAVING filters the resulting groups (only departments whose average, among those survivors, exceeds 90000). All three run in that order: WHERE, then GROUP BY, then HAVING, each one working on the output of the step before it.
SELECT department_id, AVG(salary) AS average_salary FROM employees WHERE hire_date >= '2020-01-01' GROUP BY department_id HAVING AVG(salary) > 90000;
WHERE vs HAVING
| WHERE | HAVING | |
|---|---|---|
| Filters | Individual rows | Groups, after GROUP BY |
| Runs | Before grouping | After grouping |
| Can reference an aggregate like COUNT()/AVG()? | No | Yes |
A query can use both at once, as above, they're not alternatives to each other, they filter at two different stages.
Try It Yourself
Write a query returning department names with more than one employee, using a join, GROUP BY, and HAVING together.
SELECT d.name AS department_name, COUNT(e.id) AS headcount FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.name HAVING COUNT(e.id) > 1;
Frequently Asked Questions
Can HAVING be used without GROUP BY?
Technically yes, in which case the entire table is treated as one single group, but it's rarely useful that way, HAVING almost always appears alongside an explicit GROUP BY.
Can HAVING filter on a column that isn't an aggregate?
Yes, HAVING department_id = 1 works fine, though WHERE department_id = 1 is the better choice when a condition doesn't involve an aggregate, since WHERE filters earlier, before grouping, which is cheaper.
Common Beginner Mistakes
Writing WHERE COUNT(*) > 2 instead of HAVING COUNT(*) > 2
This is a hard error in every database in this course, WHERE runs before grouping even happens, so there's no COUNT(*) result yet for it to compare against. Any condition on an aggregate result belongs in HAVING, never WHERE.
Using HAVING for a condition that doesn't need an aggregate
HAVING works, but it's the wrong tool, it filters after grouping, which means rows that WHERE could have excluded earlier (and cheaper) still get grouped first for no reason. Put non-aggregate conditions in WHERE.
Best Practices
- Use WHERE for row-level conditions and HAVING only for conditions on an aggregate result.
- Remember the order: WHERE, then GROUP BY, then HAVING, each filtering or shaping the output of the step before it.
- Recompute the aggregate expression in HAVING (COUNT(*), AVG(salary)) rather than trying to reuse a SELECT alias, since not every database allows referencing a SELECT alias inside HAVING.
Interview Questions
What's the difference between WHERE and HAVING?
WHERE filters individual rows before GROUP BY runs, and can't reference an aggregate function. HAVING filters groups after GROUP BY has collapsed rows, and is specifically meant for conditions on aggregate results like COUNT() or AVG() that don't exist yet at the row level.
Write a query that finds every department with an average salary above 90000, only counting employees hired since 2020.
SELECT department_id, AVG(salary) AS average_salary FROM employees WHERE hire_date >= '2020-01-01' GROUP BY department_id HAVING AVG(salary) > 90000; — WHERE restricts which rows are grouped in the first place, HAVING then filters the resulting per-department averages.
Summary
HAVING filters groups by an aggregate condition, after GROUP BY has already collapsed rows, exactly what WHERE can't do since WHERE only ever sees individual rows before any grouping happens. A single query can combine WHERE (row-level), GROUP BY (collapsing), and HAVING (group-level) together, in that order.
What's Next?
That completes Module 5, Combining and Grouping: UNION and UNION ALL for stacking query results, and GROUP BY with HAVING for per-group aggregates. Module 6, Subqueries and Advanced SELECT, is next, starting with EXISTS, ANY, and ALL.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.