BrevFeed
Sign in

23. GROUPING SETS, ROLLUP, CUBE

📖 Reading · 7 min
💡 Most code boxes below are live — edit one and hit Run. Boxes without a Run button are reference-only (they need a real Postgres server).

The problem: multiple levels of aggregation, one report

Say you want a report showing revenue by (store, rating), but also the subtotal per store regardless of rating, and the grand total — all in one result set, the shape a spreadsheet pivot table or a financial report typically wants. With plain GROUP BY, that's three separate queries UNIONed together:

SELECT store_id, rating, sum(amount) FROM ... GROUP BY store_id, rating
UNION ALL
SELECT store_id, NULL, sum(amount) FROM ... GROUP BY store_id
UNION ALL
SELECT NULL, NULL, sum(amount) FROM ...;

Verbose, and the table gets scanned three separate times. GROUPING SETS, ROLLUP, and CUBE are SQL-standard extensions to GROUP BY that express exactly this "multiple levels in one pass" idea directly.

GROUPING SETS: name the exact combinations you want

SELECT store_id, rating, sum(amount) AS revenue
FROM payment p
JOIN rental r ON r.rental_id = p.rental_id
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film f ON f.film_id = i.film_id
GROUP BY GROUPING SETS (
    (store_id, rating),   -- level 1: full detail
    (store_id),           -- level 2: subtotal per store
    ()                    -- level 3: grand total
);

Each parenthesized list is one "grouping" — the query computes all of them and stacks the results together in one pass. () (empty parentheses) means "group by nothing," i.e. the grand total across everything. Columns not part of a given grouping come back as NULL in that grouping's rows — this is the mechanism, and it introduces a real ambiguity worth flagging now and resolving properly in the next section: a NULL in a GROUPING SETS result can mean either "this is a subtotal row, this column doesn't apply here" or "the actual data value was NULL." GROUPING() (below) disambiguates.

ROLLUP: hierarchical subtotals, shorthand

ROLLUP(a, b, c) is shorthand for a specific, common pattern of grouping sets: the full combination, then progressively drop columns from the right, ending at the grand total — i.e. it assumes a hierarchy where a is the "outermost" level:

-- Equivalent to GROUPING SETS ((store_id, rating), (store_id), ())
SELECT store_id, rating, sum(amount) AS revenue
FROM payment p
JOIN rental r ON r.rental_id = p.rental_id
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film f ON f.film_id = i.film_id
GROUP BY ROLLUP (store_id, rating);

Use ROLLUP when your grouping columns have a genuine hierarchy (year → month → day; country → state → city; store → rating, as above) and you want subtotals at each level rolling up to a grand total — which is the overwhelming majority of real "multi-level subtotal" reports.

CUBE: every possible combination

CUBE(a, b) computes every subset of the grouping columns — (a,b), (a), (b), and () — not just the hierarchical rollup path:

-- Adds a (rating) subtotal — "total revenue per rating, across all
-- stores" — which ROLLUP's store-first hierarchy doesn't produce.
SELECT store_id, rating, sum(amount) AS revenue
FROM payment p
JOIN rental r ON r.rental_id = p.rental_id
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film f ON f.film_id = i.film_id
GROUP BY CUBE (store_id, rating);

CUBE is the right tool when the grouping columns are genuinely independent dimensions (no hierarchy) and you want every possible cross-cutting subtotal — the name comes from picturing an n-dimensional cube of every combination. It produces more rows than ROLLUP for the same input columns (2^n groupings vs. n+1), so use it deliberately, not as a default.

GROUPING(): disambiguating "subtotal NULL" from "real NULL"

SELECT
    store_id, rating, sum(amount) AS revenue,
    GROUPING(store_id) AS store_is_subtotal,
    GROUPING(rating) AS rating_is_subtotal
FROM payment p
JOIN rental r ON r.rental_id = p.rental_id
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film f ON f.film_id = i.film_id
GROUP BY ROLLUP (store_id, rating);

GROUPING(col) returns 1 if that row is a subtotal/grand-total row where col was rolled up (its NULL is structural), and 0 if the row represents an actual, specific group (any NULL there would be real data). This is the standard way to label subtotal rows in a report — e.g. CASE WHEN GROUPING(rating) = 1 THEN 'All Ratings' ELSE rating END.

Check yourself

  1. What's the shorthand relationship between ROLLUP(a, b) and an equivalent GROUPING SETS expression?
  2. Why does CUBE(a, b) produce more grouping combinations than ROLLUP(a, b) for the same two columns?
  3. In a ROLLUP result, how do you tell apart a row where rating is NULL because it's a genuine data value, versus NULL because that row is a subtotal across all ratings?
← Previous Next →