I need one answer per department
HR sends Harry the employees table and a request: “How many people are in each department?” Not one number for the whole company — one number per department.
I need one answer per department, not one for the whole company. COUNT(*) only gives me a single total, though.
Right, COUNT(*) on its own always collapses to one row. What if you just ask for the department name next to it?
Try it: Try adding department right next to COUNT(*), with no GROUP BY.
Run your query to see results here.
That errors out. The database can’t decide which one of the five “Engineering” rows — or three “Sales” rows — to show you next to the total count. COUNT(*) wants to collapse everything into one row, but department still has 16 different values, one per employee. SQL refuses to guess which one you meant, so it makes you say how to group the rows first.
GROUP BY is that missing instruction. It tells the database: “don’t treat this as one big pile of rows — bundle the rows with the same department together first, then run the aggregate once per bundle.”
Rows ↓ Group by department ↓ Calculate COUNT(*) per group ↓ One row per department
Which rows belong together?
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY department;| department | headcount |
|---|---|
| Engineering | 5 |
| Marketing | 2 |
| Product | 3 |
| Sales | 3 |
| Support | 3 |
Sixteen employee rows went in. Five department rows came out — one per distinct value in department. Every column you select alongside GROUP BY either has to be part of the grouping (like department here) or be wrapped in an aggregate (like COUNT(*)). That’s the rule the earlier error was enforcing.
Running…
Same shape, different aggregate: swap COUNT(*) for AVG(salary) and GROUP BY hands back one average per department instead of one total. The grouping logic never changes — only what you calculate inside each group does.
Try it: What happens if you also add manager_id to the SELECT list, without adding it to GROUP BY?
Run your query to see results here.
Same error as before, for the same reason. Every department has employees reporting to different managers, so once the rows are grouped by department, manager_id no longer has a single value to show per group. If a column isn’t in GROUP BY, it has to be inside an aggregate — there’s no third option.
The Product team wants a per-department breakdown: for each department, the number of employees in it and the total salary cost of that department, sorted alphabetically by department name.
Hint 1
You need one row per department, and two different aggregates on it — what clause collapses the rows into groups before you calculate anything?
Hint 2
Headcount is a row count. Total cost is a sum over a column. Both can sit in the same SELECT list next to department.
Hint 3
SELECT department, COUNT(*) AS headcount, SUM(salary) AS total_salary FROM employees GROUP BY department ORDER BY department;
Solution
Running…
Engineering has the most people, but that alone doesn’t tell you whether it costs the most — you can only tell once you group and sum. That’s the whole point of GROUP BY: it turns “one answer for everything” into “one answer per category,” without you having to write five separate queries for five departments.
GROUP BY collapses rows that share a value into one group, then runs your aggregate once per group. Anything in the SELECT list has to be either grouped or aggregated — there’s no in-between.