GROUP BY, ROLLUP, CUBE and GROUPING SETS in BigQuery

BigQuery's GROUP BY accepts SELECT aliases, column numbers and GROUP BY ALL, and computes subtotals with ROLLUP, CUBE and GROUPING SETS. Subtotal rows have NULL in the rolled-up columns; GROUPING() tells them apart from real NULLs.

GROUP BY collapses rows that share values into one row per group, and aggregates summarize each group. BigQuery adds conveniences on top of standard SQL (GROUP BY ALL, SELECT aliases and column numbers) and supports ROLLUP, CUBE and GROUPING SETS, which compute subtotals and grand totals in the same query. The examples use four rows of sales: shoes and hats in the EU and the US.

What you can group by

Every column in SELECT that isn’t inside an aggregate must be grouped:

WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, units
FROM sales
GROUP BY region

GROUP BY ALL groups by every SELECT item that isn’t an aggregate, so you don’t repeat the list. Querylab.io’s editor offers Use GROUP BY ALL as a quick fix on a non-aggregated column, and suggests it when a GROUP BY just repeats the non-aggregated columns (see code quality):

WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, product, SUM(units) AS units
FROM sales
GROUP BY ALL
ORDER BY region, product

You can also group by a SELECT alias, which saves repeating an expression:

WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT UPPER(region) AS region_code, SUM(units) AS units
FROM sales
GROUP BY region_code
ORDER BY region_code

and by column position:

WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, product
FROM sales
GROUP BY 1, 2
ORDER BY 1, 2

Positions break silently when someone reorders the SELECT list, so prefer names or GROUP BY ALL in saved queries.

ROLLUP: subtotals along a hierarchy

ROLLUP(a, b) groups by (a, b), then by (a), then by nothing, in one result. The rolled-up columns are NULL in the subtotal rows:

WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, product, SUM(units) AS units
FROM sales
GROUP BY ROLLUP(region, product)
ORDER BY region NULLS LAST, product NULLS LAST

The rows with NULL product are the region subtotals, and the row with both NULL is the grand total. The order of the columns matters: ROLLUP(region, product) gives region subtotals, ROLLUP(product, region) product subtotals.

CUBE: every combination

CUBE(a, b) adds subtotals for every combination of the columns: (a, b), (a), (b) and (). With two columns that adds product totals across regions:

WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, product, SUM(units) AS units
FROM sales
GROUP BY CUBE(region, product)
ORDER BY region NULLS LAST, product NULLS LAST

The number of groupings doubles with each column you add, so CUBE over five columns computes 32 groupings. List the ones you need with GROUPING SETS instead.

GROUPING SETS: exactly the groupings you list

GROUPING SETS takes a list of groupings, each in parentheses, and () for the grand total:

WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
SELECT region, product, SUM(units) AS units
FROM sales
GROUP BY GROUPING SETS ((region), (product), ())
ORDER BY region NULLS LAST, product NULLS LAST

ROLLUP and CUBE are shorthand for common grouping sets.

Telling a subtotal from a real NULL

If the data itself has NULLs in a grouped column, a subtotal row and a real NULL group look the same. GROUPING(column) returns 1 when the column was rolled up in that row and 0 when it holds a real value, including a real NULL:

WITH sales AS (
  SELECT 'eu' AS region, 'shoes' AS product, 10 AS units UNION ALL
  SELECT 'eu', 'hats', 5 UNION ALL
  SELECT 'us', 'shoes', 7 UNION ALL
  SELECT 'us', 'hats', 3
)
, with_null AS (SELECT * FROM sales UNION ALL SELECT NULL, 'socks', 2)
SELECT
  region,
  GROUPING(region) AS is_total_row,
  SUM(units) AS units
FROM with_null
GROUP BY ROLLUP(region)
ORDER BY is_total_row, region NULLS LAST

Both bottom rows show NULL as the region. The one with is_total_row = 0 is the 2 units sold with no region; the one with 1 is the grand total. Use GROUPING() to label the totals, for example IF(GROUPING(region) = 1, 'All regions', region).

Cost

Grouping doesn’t change what a query reads: the bytes come from the columns you reference. ROLLUP and CUBE cost more slot time than a plain GROUP BY, because BigQuery computes each grouping, but nothing more in bytes. Running each grouping as a separate query bills the columns again for every query, with the 10 MB minimum each time. A UNION ALL of several GROUP BYs in one statement reads each column once, the same bytes as GROUPING SETS; we dry-ran both on samples.shakespeare and got 3.6 MB for each. See How a query is billed.