---
title: "GROUP BY & Aggregation"
description: "Rows fall into buckets, buckets collapse into aggregates."
source: https://vizlearn.app/cs/databases/sql-group-by
---

# 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:

```sql
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:

| order | region | channel | amount |
| ----- | ------ | ------- | -----: |
| o1    | North  | online  |    120 |
| o2    | South  | store   |     90 |
| o3    | North  | store   |     80 |
| o4    | West   | online  |     40 |
| o5    | South  | online  |     60 |
| o6    | North  | online  | `NULL` |
| o7    | West   | store   |    110 |

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:

| region | COUNT(\*) | COUNT(amount) | SUM(amount) | AVG(amount) |
| ------ | --------: | ------------: | ----------: | ----------: |
| North  |         3 |             2 |         200 |         100 |
| South  |         2 |             2 |         150 |          75 |
| West   |         2 |             2 |         150 |          75 |

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:

| Grouping                   | Key shape                       | Groups |
| -------------------------- | ------------------------------- | -----: |
| no `GROUP BY`              | all rows share one global group |      1 |
| `GROUP BY region`          | `(region)`                      |      3 |
| `GROUP BY region, channel` | `(region, channel)`             |      6 |

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:

| Parent region group | Complete `(region, channel)` group | Member rows |
| ------------------- | ---------------------------------- | ----------- |
| North: `o1, o3, o6` | `(North, online)`                  | `o1, o6`    |
| North: `o1, o3, o6` | `(North, store)`                   | `o3`        |
| South: `o2, o5`     | `(South, store)`                   | `o2`        |
| South: `o2, o5`     | `(South, online)`                  | `o5`        |
| West: `o4, o7`      | `(West, online)`                   | `o4`        |
| West: `o4, o7`      | `(West, store)`                    | `o7`        |

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.