GROUP BY & Aggregation

How many orders and how much revenue did each region produce? SQL GROUP BY answers that question by partitioning input rows on a complete grouping-key tuple, maintaining aggregates for each partition, and reducing every partition to one result row. Add another key column and an existing group can split into smaller groups.

Each row enters exactly one group for the current query. COUNT(*) counts rows; COUNT(column), SUM(column), and AVG(column) ignore NULL values in that column. These two rules—one row to one group, one group to one result row—determine both the aggregate values and the result's grain.

How SQL GROUP BY & Aggregation Works

Consider this query:

SELECT
  region,
  COUNT(*)       AS orders,
  COUNT(amount)  AS recorded_amounts,
  SUM(amount)    AS revenue,
  AVG(amount)    AS average_amount
FROM sales
GROUP BY region;

The grouping key is the tuple formed by the expressions after GROUP BY. Here the tuple has one part: (region). If the query grouped by region, channel, the key would have two parts. North / online and North / store would then be different groups even though their first component matches.

Every input row belongs to one group for this query. Within each group, the aggregates have different inputs:

• COUNT(*) sees every row.
• COUNT(amount) sees only rows where amount is not NULL.
• SUM(amount) adds the non-NULL amounts. If a group has none, the result is NULL.
• AVG(amount) is the sum of the non-NULL amounts divided by their count. Its denominator is COUNT(amount), not COUNT(*).

That last distinction is easy to miss because the two counts agree until a missing amount appears. SQL is happy to let an apparently harmless NULL expose whether you understood the denominator.

The output cardinality follows a separate rule: one result row per group. Four aggregate expressions do not produce four rows. They produce four values in each group's one row.

GROUP BY & Aggregation Step by Step

The default relation contains these seven rows, in this order:

The selected grouping key is region.

1. Row o1 opens the North group. Its accumulator becomes COUNT(*) = 1, COUNT(amount) = 1, SUM(amount) = 120.
2. Row o2 opens South with COUNT(*) = 1, COUNT(amount) = 1, SUM(amount) = 90.
3. Row o3 is not adjacent to o1, but adjacency is irrelevant. Its key is still North, so North advances to COUNT(*) = 2, COUNT(amount) = 2, SUM(amount) = 200.
4. Row o4 opens West with COUNT(*) = 1, COUNT(amount) = 1, SUM(amount) = 40.
5. Row o5 returns to South. South becomes COUNT(*) = 2, COUNT(amount) = 2, SUM(amount) = 150.
6. Row o6 returns to North with a NULL amount. COUNT(*) advances from 2 to 3, while COUNT(amount) stays 2 and SUM(amount) stays 200.
7. Row o7 returns to West. West becomes COUNT(*) = 2, COUNT(amount) = 2, SUM(amount) = 150.

The three sealed groups now finalize their averages. North computes 200 / 2 = 100, not 200 / 3. South computes 150 / 2 = 75, and West computes 150 / 2 = 75. Each group then emits one row:

The row order above is a teaching order, not a SQL promise. Add ORDER BY if the order matters.

The visualization keeps the source relation visible on the left, routes one current row into the middle group stack, and writes the reduced group on the right. After the first three rows establish the mechanism, repeated assignments run faster; the NULL update slows down so the two count rules remain explicit.

Why GROUP BY Granularity Changes the Result

The same seven rows produce three different group counts depending on the key:

Adding channel splits any region that contains both online and store sales. It cannot merge two rows that were already in different region groups, because the longer tuple still contains region. This is why grouping granularity is a data-model decision, not cosmetic query formatting: it decides what one result row means.

The split is visible in the row memberships, not just in the final counts:

North therefore changes from one three-row region group into two complete-key groups. No row disappears or appears twice: the child memberships still reconcile to o1, o3, o6. Repeating the same split for South and West explains the whole 3 → 6 change; the final 1 → 3 → 6 ledger summarizes it.

At region grain, one output row means one region. At region-and-channel grain, one output row means one pair. That distinction matters downstream: joining or summing data at incompatible grains can repeat a region-level value across several channel rows and overstate a report unless the query deliberately re-aggregates it.

No GROUP BY clause means the aggregate sees the whole relation as one global group. The result has one row and no grouping-key column. It does not mean “skip grouping logic and return the original rows.” Aggregation still collapses them.

The SQL query describes this logical result. It does not require a particular physical algorithm. A database may hash keys, sort rows, exploit an index, stream an already ordered input, combine partial aggregates in parallel, or choose another valid plan. Those plans differ in time and memory cost while returning the same logical groups.

Before shipping an aggregate query, check four things:

1. What does one result row represent?
2. Does the complete grouping key express that grain?
3. Which aggregates ignore NULL, and is their denominator correct?
4. Will a downstream join combine this result with data at the same grain?

SQL GROUP BY Edge Cases

• All-NULL measures: a two-row group with two NULL amounts returns COUNT(*) = 2, COUNT(amount) = 0, SUM(amount) = NULL, and AVG(amount) = NULL.
• NULL grouping keys: rows whose grouping key is NULL belong to one group for grouping purposes. This differs from an equality join, where NULL = NULL is not TRUE.
• Empty input with grouping: GROUP BY region has no non-empty group to emit, so it returns zero rows.
• Empty global aggregate: without GROUP BY, the same empty input returns one row: both counts are 0, while SUM(amount) and AVG(amount) are NULL.
• Unique keys: if every row has a distinct grouping key, grouping does not reduce the row count. Each one-row group still emits one row.

Common Mistakes

• Dividing SUM(amount) by COUNT(*) to reproduce AVG(amount). That is wrong as soon as amount contains NULL.
• Selecting channel beside GROUP BY region without aggregating it or including it in the grouping key. A region may contain both channels, so there is no single group-level value to return. SQL standards permit some functionally dependent projections, and dialect behavior varies; the portable rule is to make the dependency explicit.
• Assuming source adjacency defines a group. Rows with the same key belong together even when unrelated rows sit between them.
• Treating NULL as a separate group per row. All NULL values in the same grouping-key position participate in one group.
• Expecting aggregate output order without ORDER BY. A stable order in one execution is not a contract.
• Reading a drawn bucket as proof of hash aggregation. Buckets explain logical membership; the optimizer chooses the physical plan.

A Note on Simplification

This lesson models logical grouping, aggregate NULL behavior, grouping granularity, and result cardinality. It is an explainer, not a substitute for a database's formal SQL and execution-plan documentation. The canvas omits collation rules, numeric precision and overflow, DISTINCT and ordered-set aggregates, HAVING, grouping sets, functional-dependency rules, optimizer choices, indexes, parallel partial aggregation, transaction visibility, and dialect-specific behavior.