Subtotals and grand totals: ROLLUP, CUBE, GROUPING SETS

A report often needs a subtotal under each group and a total at the bottom. BigQuery computes all of those rows in one GROUP BY.

How it works

A plain GROUP BY a, b makes one set of groups. ROLLUP(a, b) makes three sets in one pass: the groups of (a, b), the groups of a alone, and one group of everything. Rows from the second set have NULL in b. The single row from the third set has NULL in both columns. CUBE(a, b) makes every combination, so it adds the groups of b alone. GROUPING SETS lets you list the sets you want.

Common mistake

A subtotal row shows NULL in the column it rolled up, and so does a group of rows where that column is NULL in the data. A filter such as category IS NULL, or a label from COALESCE, treats the two the same way. Use GROUPING() to tell them apart.

Worked example

SELECT
  vehicle_info.fuel_type AS fuel_type,
  COUNT(*) AS vehicle_count,
  GROUPING(vehicle_info.fuel_type) AS is_total
FROM `learn.mot.fact_vehicle_summary`
WHERE vehicle_info.fuel_type IN ('Electric', 'Other', 'LPG', 'Gas Bi-Fuel')
GROUP BY ROLLUP(vehicle_info.fuel_type)
ORDER BY is_total, fuel_type

ROLLUP over one column gives one row for each fuel type and one grand total row. The grand total has NULL in fuel_type and counts all 95 vehicles.

is_total is 0 on the four fuel types and 1 on the grand total, so sorting by it puts the total last.

All BigQuery lessons