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

  1. FROM takes all 8 employees.
  2. GROUP BY dept_id makes piles: department 1 (Asha, Ben, Chen, Hana), department 2 (Dara, Eli), department 3 (Fatima), and a NULL pile (Gus).
  3. For each pile, COUNT(*) and AVG(salary) are worked out: department 1 has 4 people averaging 78750.
  4. SELECT returns one row per pile: dept_id, plus the aggregates.
  5. 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

Common mistakes

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.