ROLLUP — Hierarchical Subtotals

ROLLUP is a shorthand for a specific, very common type of GROUPING SETS: a hierarchy of progressively collapsed groupings. If you have dimensions (a, b, c), then ROLLUP(a, b, c) automatically generates groupings for (a, b, c), (a, b), (a), and () — each step removing the rightmost dimension.

This maps perfectly to hierarchical data: year → quarter → month, country → region → city, category → subcategory → product. The hierarchy goes from left (coarsest) to right (finest), and ROLLUP produces every "rollup" of that hierarchy with a single keyword.

ROLLUP vs GROUPING SETS

-- Syntax:
-- GROUP BY ROLLUP(a, b, c)
-- is exactly equivalent to:
-- GROUP BY GROUPING SETS (
--     (a, b, c),
--     (a, b),
--     (a),
--     ()
-- )

ROLLUP(a, b, c) always assumes the hierarchy flows left to right. If your hierarchy is different (e.g. you want (a, c) but not (b, c)), use explicit GROUPING SETS instead.

Example 1: Sales hierarchy — Year → Quarter → Month

This is the classic use case. A finance dashboard needs revenue at every level of the time hierarchy: full monthly detail, quarterly totals, annual totals, and a grand total. One ROLLUP produces all four levels.

SELECT
    EXTRACT(YEAR  FROM created_at)::INT AS year,
    EXTRACT(QUARTER FROM created_at)::INT AS quarter,
    EXTRACT(MONTH FROM created_at)::INT AS month,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue,
    COUNT(*) AS orders,
    GROUPING(
        EXTRACT(YEAR FROM created_at),
        EXTRACT(QUARTER FROM created_at),
        EXTRACT(MONTH FROM created_at)
    ) AS level_code
FROM orders o
JOIN ec_order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY ROLLUP(
    EXTRACT(YEAR  FROM created_at),
    EXTRACT(QUARTER FROM created_at),
    EXTRACT(MONTH FROM created_at)
)
ORDER BY year NULLS LAST, quarter NULLS LAST, month NULLS LAST;

What This Returns

The level_code increases as more dimensions are collapsed: 0 = most granular, 7 = grand total.

Example 2: Geographic hierarchy — Country → City

Using the invoicing dataset: compute total revenue at each level of the client geography hierarchy. This is the kind of rollup a regional VP would want — their city details, their country total, and the global total.

SELECT
    CASE WHEN GROUPING(c.country) = 1 THEN 'GLOBAL TOTAL' ELSE c.country END AS country,
    CASE WHEN GROUPING(c.country)    = 1 THEN '(all cities)' ELSE c.country    END AS city,
    COUNT(DISTINCT c.client_id)                    AS clients,
    COUNT(DISTINCT i.invoice_id)                   AS invoices,
    ROUND(SUM(ii.quantity * ii.unit_price), 0)     AS revenue,
    ROUND((AVG(ii.quantity * ii.unit_price))::NUMERIC, 0)     AS avg_item_value
FROM clients c
JOIN invoices i  ON i.client_id  = c.client_id
JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
GROUP BY ROLLUP(c.country, c.country)
ORDER BY
    GROUPING(c.country, c.country),
    c.country NULLS LAST,
    c.country    NULLS LAST;

Note the ORDER BY GROUPING(c.country, c.country) as the first sort key — this ensures detail rows appear before subtotals and the grand total, regardless of alphabetical ordering.

Example 3: Product hierarchy with partial ROLLUP

Sometimes you want subtotals on only part of your GROUP BY. You can combine regular columns with ROLLUP to get fixed dimensions with a rollup on others:

-- Fixed dimension: year (always present)
-- Rollup dimension: category → subcategory → product
SELECT
    EXTRACT(YEAR FROM DATE_TRUNC('day', o.created_at)::DATE)::INT AS year,
    CASE WHEN GROUPING(p.category)    = 1 THEN '(ALL)' ELSE p.category    END AS category,
    CASE WHEN GROUPING(p.subcategory) = 1 THEN '(ALL)' ELSE p.subcategory END AS subcategory,
    COUNT(DISTINCT o.order_id)                     AS orders,
    SUM(oi.quantity)                               AS units_sold,
    ROUND(SUM(oi.quantity * oi.unit_price), 2)     AS revenue
FROM ec_orders o
JOIN ec_order_items oi ON oi.order_id = o.order_id
JOIN ec_products p     ON p.product_id = oi.product_id
WHERE o.status = 'completed'
GROUP BY
    EXTRACT(YEAR FROM DATE_TRUNC('day', o.created_at)::DATE),
    ROLLUP(p.category, p.subcategory)   -- rollup only product dimensions, year is fixed
ORDER BY year, GROUPING(p.category, p.subcategory), p.category NULLS LAST, p.subcategory NULLS LAST;

The year column is outside the ROLLUP, so every output row always has a year value. The rollup only happens within the product hierarchy. This gives you category/subcategory subtotals per year without a year-level rollup.

What This Returns

For each year, you get: subcategory detail rows (both category and subcategory set), category subtotals (subcategory NULL), and an all-products total (both NULL) — but never a cross-year grand total.

Example 4: ROLLUP for a management dashboard

A practical combined query that produces the full executive revenue dashboard — geographic hierarchy combined with product hierarchy — using nested rollups:

WITH revenue_base AS (
    SELECT
        c.country,
        p.category,
        DATE_TRUNC('month', DATE_TRUNC('day', o.created_at)::DATE)::DATE AS month,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN ec_order_items oi ON oi.order_id = o.order_id
    JOIN ec_products p ON p.product_id = oi.product_id
    JOIN customers c ON c.customer_id = o.customer_id
    WHERE o.status = 'completed'
    GROUP BY c.country, p.category, 3
)
SELECT
    CASE WHEN GROUPING(country)  = 1 THEN 'ALL COUNTRIES' ELSE country  END AS country,
    CASE WHEN GROUPING(category) = 1 THEN 'ALL CATEGORIES' ELSE category END AS category,
    CASE WHEN GROUPING(month)    = 1 THEN NULL ELSE month END AS month,
    ROUND(SUM(revenue), 0) AS revenue,
    ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (
        PARTITION BY GROUPING(country, category, month)
    ), 1) AS pct_of_level
FROM revenue_base
GROUP BY ROLLUP(country, category, month)
ORDER BY GROUPING(country, category, month), country, category, month;

The pct_of_level column uses a window function over PARTITION BY GROUPING(...) — so detail rows show their percentage of the detail total, subtotal rows show their percentage of the subtotal total, and so on. This is a very powerful pattern for "what % of the total does this row represent at its level?"

Key Takeaway

ROLLUP is GROUPING SETS with the hierarchy assumption baked in. Use it whenever your dimensions have a natural left-to-right hierarchy (time, geography, product taxonomy). Combine with GROUPING() to label rows correctly, and with window functions to compute percentages-of-total at each level.