GROUP BY & Aggregates
18 min
GROUP BY collapses rows sharing the same value in a column into a single
row per group — combined with an aggregate function (COUNT, SUM,
AVG, MIN, MAX) to summarize each group.
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
Every column in SELECT that ISN'T inside an aggregate function must
appear in GROUP BY — SQL has no way to pick a single representative
value for the other rows in the group otherwise.
You can compute multiple aggregates in the same GROUP BY query — one row per department, each with its own average and count.
WHERE and HAVING both filter rows, but at different stages: WHERE
runs first, filtering individual rows before grouping — so it can't
reference an aggregate like COUNT(*), which doesn't exist yet at that
point. HAVING runs after grouping, filtering entire groups — that's the
only place you can filter on an aggregate result.
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 90000 -- filters individual employees first
GROUP BY department
HAVING COUNT(*) > 1; -- then filters the resulting groups
The `employees` table has columns `id, name, department, salary`. Write a query that returns each `department` and the average salary in that department as `avg_salary`, ordered by department name.
Write a query that returns each `department` and its employee count as `employee_count`, but only for departments with MORE THAN ONE employee — ordered by employee_count descending.
Why does `WHERE COUNT(*) > 1` fail, while `HAVING COUNT(*) > 1` works?
You can group rows and compute per-group aggregates, and know the difference between filtering rows (WHERE) and filtering groups (HAVING).