skip to content

GROUP BY Semantics

16 roadmaps6 questions

What GROUP BY actually does: partition the filtered rows into groups and emit one row per group, with the whole table acting as a single group when GROUP BY is absent. I need this mental model because interviewers probe whether I can predict the exact shape of a grouped result, including where NULL keys land.

part ofSQLoverview, primer and where to startread it →
on this pageshow

explore

questions

6

What does GROUP BY do to a query's rows, and what determines the result row count?

level: juniorimportance: must knowfreq 85%

answer

  1. many rows in, few rows out
  2. one output row per group
  3. keys are matched as a whole tuple
  4. count the distinct key combinations
  5. sorting still needs ORDER BY

basics

~20 s

GROUP BY partitions the rows that survive FROM and WHERE into groups sharing the same grouping-key values, then emits exactly one row per group. The result has as many rows as there are distinct key combinations.

solid answer

~50 s

`GROUP BY` collapses rows. Once `FROM` and `WHERE` have produced a working set, the engine partitions that set on the tuple of grouping-key values: rows whose key values are equal land in the same group. Each group then yields exactly one output row, and any aggregate in the `SELECT` list — `COUNT(*)`, `SUM(amount)` — is computed over that group's rows only. So `GROUP BY region` returns one row per distinct region present in the filtered data, and `GROUP BY region, status` returns one row per distinct `(region, status)` pair that actually occurs — not the full cross product of regions and statuses. Adding a grouping key can only leave the group count the same or raise it, never lower it. And nothing about `GROUP BY` sorts anything: if you want a stable order, write `ORDER BY`.

code

sql · 8 lines
sql
-- One output row per distinct region present after the WHERE filter
SELECT region,
       COUNT(*)   AS order_count,
       SUM(amount) AS revenue
FROM orders
WHERE order_date >= DATE '2026-01-01'
GROUP BY region
ORDER BY revenue DESC;

go deeper

for a junior

Be ready to state the model in one sentence and predict a row count: rows are partitioned by the grouping keys and each partition yields one row. Practise on a small table until you can call the answer before running the query.

for a middle

Explain the mechanics: the key is a tuple compared position by position, adding a key subdivides groups, and the output cardinality equals the number of distinct tuples in the grouping input. Know that ordering is undefined without ORDER BY.

for a senior

Show that you debug grouped reports by cardinality. When a summary has the wrong number of rows, you reason backwards to the grouping input — a changed key list, or joined rows multiplying before the grouping step — instead of patching the numbers downstream.

for a principal

Own the reporting contract: which key tuple defines a fact's grain, and what it costs when two teams group the same data at different grains. Grain drift, not syntax, is what makes summary numbers disagree across an organisation.

## The one-sentence model `GROUP BY` turns many rows into few. It takes the rows a query has already assembled, partitions them into groups, and emits exactly one row per group. Everything else about grouping follows from that sentence. ## Where grouping sits By the time `GROUP BY` runs, `FROM` has built the source rows (including any join output) and `WHERE` has discarded the rows whose predicate did not evaluate to TRUE. Grouping never sees the rows `WHERE` removed and cannot bring them back. Whatever row set arrives is the input to the partitioning step. ## The grouping key is a tuple You can name one key or several: ```sql GROUP BY region GROUP BY region, status ``` With several keys, the group identity is the whole tuple of key values, compared position by position. Two rows share a group when *all* their key values match. `('EU', 'paid')` and `('EU', 'refunded')` are different groups; `('EU', 'paid')` and `('EU', 'paid')` are the same one. Grouping compares key values as *not distinct*, which also means two NULL keys count as the same value rather than as an unknown comparison. ## One row out per group Each group produces exactly one row. Inside that row you can project the grouping keys — they have a single value for the whole group, by construction — and aggregates such as `COUNT(*)`, `SUM(amount)`, `MIN(created_at)`, each evaluated over just that group's rows. The individual rows themselves are gone: after grouping there is no way to point at "the third order in this region". If you need detail rows *and* a per-group total in the same result, `GROUP BY` is the wrong instrument — that is what a window function is for. ## Predicting the row count The output row count is the number of distinct key combinations present in the grouping input: - `GROUP BY region` over rows containing 4 distinct regions returns 4 rows. - `GROUP BY region, status` over the same rows returns the number of `(region, status)` pairs that actually occur — at most 4 × (number of statuses), usually fewer, because absent combinations simply do not exist as groups. - The count is bounded above by the number of input rows: grouping can never invent rows. - Adding a grouping key subdivides existing groups, so the count is monotonically non-decreasing as you add keys. Removing a key merges groups and the count falls or stays put. This last property is the everyday debugging tool: if a report suddenly has ten times as many rows as expected, look for a grouping key that was added (or a join that multiplied the input rows before grouping). ## GROUP BY never invents groups Grouping is driven entirely by the data. A region with no matching rows produces no group, and therefore no row — there is no "zero" row unless you manufacture the key values from somewhere else, such as a dimension table. ## Ordering is not implied The SQL standard specifies no ordering for a grouped result. Some engines happen to return groups in key order for some plans, which is exactly the kind of accident that breaks when the data grows or the plan changes. Write `ORDER BY` when order matters. The same holds for the order of rows *inside* a group: it is undefined, which is why aggregates that care about order (string concatenation, for example) take their own `ORDER BY` inside the function call. ## Worked example Given `orders`: | id | region | status | amount | |----|--------|--------|--------| | 1 | EU | paid | 10 | | 2 | EU | paid | 20 | | 3 | EU | refunded | 5 | | 4 | US | paid | 40 | ```sql SELECT region, COUNT(*) AS n, SUM(amount) AS total FROM orders GROUP BY region; ``` returns two rows: `('EU', 3, 35)` and `('US', 1, 40)`. Change the clause to `GROUP BY region, status` and you get three rows — `('EU','paid',2,30)`, `('EU','refunded',1,5)`, `('US','paid',1,40)` — because the second key splits the EU group in two. There is no `('US','refunded')` row: that pair never occurs in the data, so no such group exists. ## The mental checklist Before running a grouped query, answer three questions: which rows reach the grouping step, what is the key tuple, and how many distinct tuples does the data contain? Those three answers fully determine the shape of the result.

  • If a table holds 4 regions and 3 statuses, how many rows can GROUP BY region, status return?
    Between 0 and 12. Twelve is the ceiling — the product of the distinct values — but grouping only produces groups for combinations that actually occur in the filtered rows, so absent pairs are simply missing. If the filter removes everything, the query returns no rows at all.
  • After grouping, can the SELECT list still reach an individual row's columns?
    No. Once rows are partitioned, only two things have a single well-defined value per output row: the grouping keys, and aggregates computed over the group's rows. Detail columns no longer identify anything. When you need detail rows alongside a per-group total, use a window function instead of collapsing with GROUP BY.
  • Can a grouped query ever return more rows than its input?
    No. Each output row corresponds to a distinct key tuple, and every tuple comes from at least one input row, so the group count is at most the input row count. Equality happens only when every input row has a unique key combination — which usually means the grouping key is too fine to be useful.

Think of sorting a stack of receipts into pigeonholes labelled by region, then writing a single summary card for each pigeonhole. The receipts stay in the box; only the cards come out, one per pigeonhole that has anything in it.

saying these in an interview costs you the question

  • Says GROUP BY sorts the output so ORDER BY is unnecessary
  • Thinks GROUP BY filters rows out instead of collapsing them
  • Expects GROUP BY a, b to return every combination of a and b
  • Believes adding a grouping key reduces the number of result rows
  • Assumes detail columns remain visible beside the aggregates

context

resolving links…

open as a page

How does GROUP BY treat rows whose grouping key is NULL, and how many groups do they form?

level: middleimportance: must knowfreq 60%

basics

~20 s

GROUP BY treats two NULLs as not distinct, so all rows with a NULL key collapse into one group whose key prints as NULL — even though NULL = NULL evaluates to UNKNOWN in a WHERE predicate.

open as a page

Can GROUP BY take an expression instead of a column, and how do you bucket a timestamp by day?

level: middleimportance: should knowfreq 60%

basics

~20 s

Yes — GROUP BY accepts any value expression over the input columns, and groups are keyed on the computed value. Bucketing a timestamp by day means grouping on a truncated form, such as CAST(created_at AS DATE), not on the raw timestamp.

open as a page

Why does SELECT COUNT(*), SUM(amount) FROM orders WHERE region = 'ZZ' return a row when nothing matches?

level: middleimportance: should knowfreq 55%

basics

~20 s

With no GROUP BY, an aggregate query treats the whole filtered input as one implicit group and always returns exactly one row, even if nothing matched. Add GROUP BY and empty input yields zero groups, so zero rows.

open as a page

What does GROUP BY do when the SELECT list has no aggregate, and how does it compare with SELECT DISTINCT?

level: middleimportance: should knowfreq 50%

basics

~20 s

Grouping still happens: rows are partitioned by the key and each group emits its key, so the result is the distinct key combinations — the same row set SELECT DISTINCT would give over those columns. GROUP BY additionally supports aggregates and group-level filtering.

open as a page

Why does a GROUP BY report skip days with no sales, and how do you emit a row for every day?

level: seniorimportance: should knowfreq 45%

basics

~20 s

GROUP BY forms groups only from key values present in its input, so a day with no rows produces no group and no output row. To show every day you must supply the key values from elsewhere — typically a calendar table left-joined to the aggregated result.

open as a page