SQL ROLLUP: Hierarchical Subtotals and a Grand Total in One Query

ROLLUP is an extension of GROUP BY that, in a single query, computes not just the ordinary groups but also subtotals over column prefixes plus one grand-total row. It is a ready-made engine for reports like "revenue by month and region, with subtotals and a final sum."

What ROLLUP computes

GROUP BY ROLLUP (a, b) aggregates at several levels, dropping columns right to left: first (a, b), then (a), then () the empty set, meaning the whole dataset. For two columns that gives you the detail rows, subtotals per a, and a single grand-total row.

SELECT
  country,
  status,
  SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP (country, status)
ORDER BY country, status;

On top of the ordinary (country, status) pairs, the result adds:

• one row per country where status is NULL — that is the per-country subtotal;
• one row where both country and status are NULL — the grand total over all orders.

Column order matters: ROLLUP (country, status) and ROLLUP (status, country) produce different subtotals, because the hierarchy collapses from right to left.

Reading trailing NULLs with GROUPING()

The main gotcha: subtotal rows are marked with NULL in the collapsed columns. But if your data already contains NULL (say, an order with no country), you cannot tell a real NULL from a subtotal marker. The GROUPING() function fixes this: it returns 1 when a column was rolled up in that row, and 0 when it holds a real value.

SELECT
  GROUPING(country)             AS is_total_country,
  GROUPING(status)              AS is_total_status,
  country,
  status,
  SUM(amount)                   AS revenue
FROM orders
GROUP BY ROLLUP (country, status)
ORDER BY GROUPING(country), country, GROUPING(status), status;

It is cleaner to swap the flags for readable labels right away, so the report needs no footnotes:

SELECT
  CASE WHEN GROUPING(country) = 1 THEN 'All countries' ELSE country END AS country,
  CASE WHEN GROUPING(status)  = 1 THEN 'All statuses'  ELSE status  END AS status,
  SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP (country, status)
ORDER BY country NULLS LAST, status NULLS LAST;

A month/region drill-down report

A classic management report is revenue broken down by month and region, showing the detail, the per-month subtotal, and the grand total. Here ROLLUP pairs nicely with DATE_TRUNC.

SELECT
  CASE WHEN GROUPING(DATE_TRUNC('month', o.created_at)) = 1
       THEN 'All months'
       ELSE TO_CHAR(DATE_TRUNC('month', o.created_at), 'YYYY-MM') END AS month,
  COALESCE(u.country, 'All regions') AS region,
  SUM(o.amount)                      AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY ROLLUP (DATE_TRUNC('month', o.created_at), u.country)
ORDER BY DATE_TRUNC('month', o.created_at) NULLS LAST, u.country NULLS LAST;

Each month gets its per-region rows, then a month subtotal (region = 'All regions'), and at the very bottom All months with the full revenue. Ordering with NULLS LAST guarantees the subtotals land below their detail rows rather than above them.

Relation to GROUPING SETS

ROLLUP is just shorthand for GROUPING SETS. The query GROUP BY ROLLUP (a, b) is strictly equivalent to:

GROUP BY GROUPING SETS ((a, b), (a), ());

Knowing this buys you flexibility:

• want CUBE? That is GROUPING SETS over every combination: (a, b), (a), (b), ();
• want only detail and a grand total, with no intermediate subtotals? Spell out GROUPING SETS ((a, b), ()) by hand;
• you can combine several ROLLUP clauses and plain columns inside one GROUP BY.

On dialects: ROLLUP, CUBE and GROUPING SETS are supported in PostgreSQL 9.5+. MySQL offers only GROUP BY ... WITH ROLLUP (no CUBE, no explicit GROUPING SETS), and ORDER BY together with WITH ROLLUP is finicky. ClickHouse has WITH ROLLUP, WITH CUBE and WITH TOTALS modifiers, but their syntax and row ordering differ from the standard. If you port a report across engines, check the total rows specifically.