GROUP BY splits rows into buckets that share a value, then computes an aggregate for each bucket — 'salary per department', 'orders per customer'. HAVING then filters those grouped results, the way WHERE filters individual rows.
Group, aggregate, then filter groups
After GROUP BY dept_id, each department becomes one result row and you can apply an aggregate to it. WHERE filters rows before grouping; HAVING filters groups after.
Average salary per department, busy departments only
SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_sal FROM employees WHERE salary > 0 -- filter rows first GROUP BY dept_id HAVING COUNT(*) >= 2 -- then filter the groups ORDER BY avg_sal DESC;
⚡ The edge
- WHERE filters rows before grouping; HAVING filters groups after — aggregates (COUNT, AVG...) can only appear in HAVING, never in WHERE.
- Every column in SELECT must be either in the GROUP BY or wrapped in an aggregate — otherwise the query is ambiguous and (in standard SQL) an error.
Worked example
Show each department with more than one employee, and its average salary.
- Group the rows by department: GROUP BY dept_id.
- Compute the average salary per group with AVG(salary), and count members with COUNT(*).
- Keep only groups with COUNT(*) > 1 using HAVING.
Answer: SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id HAVING COUNT(*) > 1;
Worked example
Why does WHERE COUNT(*) > 2 fail?
- WHERE is evaluated before rows are grouped, so no aggregate (COUNT) exists yet.
- Aggregates are computed during grouping, so a condition on them must come after.
- That post-grouping filter is exactly what HAVING is for.
Answer: Aggregates don't exist at WHERE-time; filter groups with HAVING COUNT(*) > 2 instead.
⚠ Watch out
- You cannot use an aggregate in WHERE — it belongs in HAVING.
- Every non-aggregated SELECT column must appear in GROUP BY.
- HAVING without GROUP BY treats the whole result as one group — valid but rarely intended.