GROUP BY: one result row per group
GROUP BY splits the rows into groups that share the same value in the listed columns, and the aggregates then return one row per group.
Why it matters
One total is rarely enough. Managers ask for "revenue per month", "headcount per department", "orders per customer". GROUP BY gives you a total for every group in one query, instead of one query per group.
The idea
GROUP BY dept_id puts all rows with the same dept_id into one pile. Each pile becomes one result row, and every aggregate in SELECT is worked out separately for each pile.
The golden rule: every column in SELECT must either be in GROUP BY or be inside an aggregate. Why? A group of 4 employees has 4 names, and the database can't pick one for you. All NULLs fall into one group of their own.
Sorting a bag of coins into piles by type — all the 10p together, all the £1 together — and then counting each pile. GROUP BY makes the piles; COUNT and SUM count them.
With coins you can still see each coin in its pile. After GROUP BY, the separate rows are gone from the result: you only see one row per pile, so you can't ask for "the name" of a group unless you aggregate it.
Syntax
SELECT group_column, COUNT(*), SUM(column)
FROM table_name
GROUP BY group_column;How it works
- FROM takes all 8 employees.
- GROUP BY dept_id makes piles: department 1 (Asha, Ben, Chen, Hana), department 2 (Dara, Eli), department 3 (Fatima), and a NULL pile (Gus).
- For each pile,
COUNT(*)andAVG(salary)are worked out: department 1 has 4 people averaging 78750. - SELECT returns one row per pile: dept_id, plus the aggregates.
- Department 4 (HR) has no employees, so there is no pile and no row for it. Getting zero rows to show needs a join (Unit 3).
Key points
- GROUP BY makes one result row per distinct combination of the grouping columns.
- Every column in SELECT must be in GROUP BY or inside an aggregate.
- All NULLs in a grouping column form one group.
- Groups with no rows don't appear at all. To show zeros (like HR with 0 people), start from the full list and LEFT JOIN (Unit 3).
- Grouped results come back in no particular order. Add ORDER BY.
Common mistakes
- Selecting a column that is neither grouped nor aggregated, like
SELECT dept_id, name, COUNT(*) … GROUP BY dept_id. - Expecting groups with zero rows to appear with a count of 0.
- Grouping by too many columns (such as the id), so every group has one row and nothing is summarised.
Not true: GROUP BY also returns the groups that have no rows, with a count of 0.
In fact: A group exists only if at least one row falls into it. To show a zero, start from the full list (such as all departments) and LEFT JOIN onto it (Unit 3).
In the interview
GROUP BY partitions the filtered rows into groups by the grouping columns, and aggregates are computed once per group, giving one output row per group. The rule is that every selected column must be grouped or aggregated. Two details I mention. NULLs form a single group. And groups with no rows simply don't appear, so to report zeros I drive the query from the complete dimension table with a LEFT JOIN.
Part of Unit 2: Summarising data: aggregates, GROUP BY and HAVING in the free SQL course.