ROLLUP, CUBE, and GROUPING SETS

Multi-level aggregation with ROLLUP, CUBE, GROUPING SETS, and the GROUPING() function.

When you want subtotals at multiple granularities (region × day, region, day, grand total), ROLLUP and CUBE avoid writing the same query four times with different GROUP BY clauses.

ROLLUP

ROLLUP(a, b, c) produces the hierarchy (a, b, c), (a, b), (a), (). Each rollup level adds one fewer column to the group-by.

SELECT
  region, country, category,
  SUM(amount) AS total,
  COUNT(*)    AS n
FROM orders
GROUP BY ROLLUP(region, country, category)
ORDER BY region NULLS LAST, country NULLS LAST, category NULLS LAST;

Output rows have NULL in columns that were rolled up. The grand-total row is all NULLs.

CUBE

CUBE(a, b, c) produces every combination: 2³ = 8 rows of grouping (including the grand total).

SELECT region, category, SUM(amount) AS total
FROM orders
GROUP BY CUBE(region, category);

Use CUBE for OLAP cubes where every combination is meaningful.

GROUPING SETS

Explicit list of grouping sets, when ROLLUP / CUBE produce too many:

SELECT region, country, category, SUM(amount) AS total
FROM orders
GROUP BY GROUPING SETS (
  (region, country, category),
  (region, country),
  (region),
  ()
);

Equivalent to writing four separate UNION ALL queries — but with the cost of a single scan.

The GROUPING() function

Distinguish a rollup-level NULL (which means "rolled up") from a data NULL (which means "value was NULL in the source row):

SELECT
  region, country,
  GROUPING(region)   AS region_is_rollup,
  GROUPING(country)  AS country_is_rollup,
  SUM(amount)        AS total
FROM orders
GROUP BY ROLLUP(region, country);

GROUPING(col) returns 1 if col was rolled up in this row, 0 otherwise. GROUPING_ID(a, b, c) returns a bitmask if you prefer a single column.

Filtering rollup rows

Often you want to drop the subtotals and keep only the leaf level, or vice versa:

-- Only the grand total
HAVING GROUPING_ID(region, country) = 3;

-- Only the leaves (no rollups)
HAVING GROUPING(region) = 0 AND GROUPING(country) = 0;

With incremental views

ROLLUP / CUBE / GROUPING SETS work inside CREATE INCREMENTAL VIEW. The IVM runtime tracks each grouping set independently and emits per-set deltas.