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.
GROUP BY ROLLUP(fuel_type, category)returns the groups of(fuel_type, category), a subtotal for eachfuel_type, and one grand total row. The order matters:ROLLUP(category, fuel_type)gives subtotals for each category instead.GROUP BY CUBE(a, b)returns(a, b),(a),(b)and the grand total. Every extra column doubles the number of sets, soCUBEover three columns computes eight.GROUP BY GROUPING SETS ((a), (b), ())returns exactly the sets you list, and()is the grand total.ROLLUPandCUBEare shorthand for common lists of sets.GROUPING(a)is 1 on a row whereawas rolled up, and 0 on every other row, including a row whoseaisNULLin the data. Pass it the same expression you group by. It works inSELECT,HAVINGandORDER BY.- Without an
ORDER BYthe order of the rows is not defined.ORDER BY GROUPING(a), aputs the subtotal rows after the detail rows.
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_typeROLLUP 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.