Advanced Grouping: Pivots and Subtotals

You will group by several columns or an expression, turn row values into columns with conditional aggregation, and build subtotal and grand-total rows in SQLite.

SQL Fundamentals grouped by one column and filtered piles with HAVING; Set Operations stacked results with UNION ALL. This lesson shapes the piles and the output columns. The examples use a seven-row parcels table (id, depot, kind, status, weight_kg, shipped_on); one parcel has a NULL depot.

Several columns, or an expression, as the key

Every distinct combination of the key values is one group, and a key can be an expression:

SELECT depot, strftime('%Y-%m', shipped_on) AS month, COUNT(*) AS n
FROM parcels
GROUP BY depot, month
ORDER BY depot, month;

|2026-02|1
Hull|2026-01|1
Hull|2026-02|2
Leeds|2026-01|2
Leeds|2026-02|1

The NULL depot is its own group (blank above) and sorts first. SQLite lets GROUP BY use the alias month or a position like GROUP BY 1, 2; PostgreSQL rejects an alias in HAVING and SQL Server rejects both, so repeat the expression for portable SQL.

CASE, and counting inside a group

CASE WHEN condition THEN value ... ELSE value END is an expression: it returns the THEN of the first condition that holds, and NULL if none holds and there is no ELSE. As a key it buckets rows (GROUP BY a CASE WHEN weight_kg >= 5 ... gives heavy and light piles). Inside an aggregate, each group counts only the rows you pick; this is conditional aggregation:

SELECT depot,
       COUNT(*) AS parcels,
       SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS with_else,
       SUM(CASE WHEN status = 'returned' THEN 1 END)        AS no_else,
       COUNT(CASE WHEN status = 'returned' THEN id END)     AS count_id,
       COUNT(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS count_else
FROM parcels GROUP BY depot ORDER BY depot;

|1|0||0|1
Hull|3|0||0|3
Leeds|3|1|1|1|3

Read the Hull line. no_else is blank: every CASE gave NULL, so SUM had nothing to add and returned NULL, not 0. COUNT skips NULLs, so it needs no ELSE; with ELSE 0 the 0 is a value and count_else counts every row. SUM wants ELSE 0; COUNT wants none.

Pivots: one column per value

A pivot (cross-tab) turns the values of a column into output columns, one conditional aggregate each:

SELECT depot,
       SUM(CASE WHEN kind = 'box' THEN weight_kg ELSE 0 END)      AS box_kg,
       SUM(CASE WHEN kind = 'envelope' THEN weight_kg ELSE 0 END) AS env_kg,
       SUM(weight_kg) AS all_kg
FROM parcels GROUP BY depot ORDER BY depot;

|7|0|7
Hull|11|2|13
Leeds|7|1|8

The columns are fixed in the query text; SQL cannot invent one per value it finds. PostgreSQL also offers COUNT(*) FILTER (WHERE status = 'returned'), but SQLite only added it in 3.30 and the grader runs 3.27.2, where it is an error. Use CASE.

Subtotals and grand totals

PostgreSQL has GROUP BY ROLLUP (depot, kind), CUBE and GROUPING SETS for detail, subtotal and grand-total rows. SQLite has none and errors. Stack several GROUP BY queries with UNION ALL instead. The naive version has a trap:

SELECT depot, kind, SUM(weight_kg) AS kg FROM parcels GROUP BY depot, kind
UNION ALL
SELECT depot, NULL, SUM(weight_kg) FROM parcels GROUP BY depot
UNION ALL
SELECT NULL, NULL, SUM(weight_kg) FROM parcels
ORDER BY depot, kind;

||7
||28
|box|7
Hull||13
...

The NULL depot's subtotal (7) and the grand total (28) are both NULL|NULL, and both sort to the top. A subtotal's NULL means "all values"; a real NULL means "unknown". Add a marker column recording which query made each row, and sort on it. A compound ORDER BY must name a result column, so wrap the query; the outer one can sort by the marker without printing it:

SELECT CASE WHEN lvl = 2 THEN 'ALL DEPOTS' ELSE COALESCE(depot, '(unknown)') END AS depot,
       COALESCE(kind, 'subtotal') AS kind, kg
FROM (
  SELECT depot, kind, SUM(weight_kg) AS kg, 0 AS lvl FROM parcels GROUP BY depot, kind
  UNION ALL
  SELECT depot, NULL, SUM(weight_kg), 1 FROM parcels GROUP BY depot
  UNION ALL
  SELECT NULL, NULL, SUM(weight_kg), 2 FROM parcels
)
ORDER BY lvl = 2, depot IS NULL, depot, lvl, kind;

Hull|box|11
Hull|envelope|2
Hull|subtotal|13
Leeds|box|7
Leeds|envelope|1
Leeds|subtotal|8
(unknown)|box|7
(unknown)|subtotal|7
ALL DEPOTS|subtotal|28

lvl = 2 is 0 or 1, so the grand total goes last; depot IS NULL puts the unknown depot after the named ones.

group_concat(depot) joins a group's values into one string, skipping NULLs. The order of the pieces is unspecified, so never rely on it in output you compare exactly.

Common mistakes

• SUM(CASE WHEN ... THEN x END) with no ELSE: a group with no matching rows prints a blank instead of 0.
• Sorting subtotals by their NULL key or by label: the grand total prints first, or a label like TOTAL lands alphabetically in the middle of the names.
• COUNT(CASE WHEN c THEN 1 ELSE 0 END): counts every row, because 0 is not NULL.
• Adding a column to GROUP BY that is not a key: you get one line per combination instead of one per group.

Your exercise

The sales table has a row with a NULL region and regions that sold in only one quarter. Print a Q1 / Q2 / total line per region, then a TOTAL line, in the stated order. The starter prints one total per region; the most likely mistake is a blank where a quarter has no sales.