GROUPING SETS, ROLLUP, and CUBE

Standard GROUP BY computes one level of aggregation. But business reports often need subtotals (by region), cross-tabulations (by region AND product), and grand totals -- all in a single result set. PostgreSQL's GROUPING SETS, ROLLUP, and CUBE do exactly that.

The Problem: Multiple Aggregation Levels

Suppose you want a sales report showing:

1. Totals by region and payment method (detail rows).
2. Totals by region alone (subtotals).
3. A grand total across everything.

Without GROUPING SETS, you would need three separate queries combined with UNION ALL:

-- The hard way
SELECT region, payment_method, SUM(amount) FROM sales GROUP BY region, payment_method
UNION ALL
SELECT region, NULL,           SUM(amount) FROM sales GROUP BY region
UNION ALL
SELECT NULL,   NULL,           SUM(amount) FROM sales;

This scans the table three times. There is a better way.

GROUPING SETS

GROUPING SETS lets you specify exactly which groupings you want in a single query:

SELECT
  region,
  payment_method,
  SUM(amount) AS total_amount,
  COUNT(*)    AS num_sales
FROM sales
GROUP BY GROUPING SETS (
  (region, payment_method),  -- detail: region + payment method
  (region),                  -- subtotal: region only
  ()                         -- grand total
)
ORDER BY region NULLS LAST, payment_method NULLS LAST;

Sample output:

Rows where payment_method is NULL are subtotals for that region. The row where both columns are NULL is the grand total.

Custom Grouping Combinations

You can specify any combination of groupings:

-- Only subtotals by region and by payment_method, plus grand total
-- (no detail rows)
SELECT
  region,
  payment_method,
  SUM(amount) AS total_amount
FROM sales
GROUP BY GROUPING SETS (
  (region),
  (payment_method),
  ()
)
ORDER BY region NULLS LAST, payment_method NULLS LAST;

ROLLUP -- Hierarchical Subtotals

ROLLUP is a shorthand for the most common pattern: hierarchical subtotals from left to right, ending with a grand total.

ROLLUP(a, b, c)
-- is equivalent to:
GROUPING SETS (
  (a, b, c),
  (a, b),
  (a),
  ()
)

Example: Sales Hierarchy

SELECT
  region,
  payment_method,
  SUM(amount) AS total_amount,
  COUNT(*)    AS num_sales
FROM sales
GROUP BY ROLLUP(region, payment_method)
ORDER BY region NULLS LAST, payment_method NULLS LAST;

This produces:

• Detail rows (region + payment_method)
• Subtotals per region
• Grand total

Think of ROLLUP like a report with expanding summaries: the leftmost column is the broadest category, and each additional column adds detail.

Three-Level ROLLUP

-- If we had year data, this is how a 3-level rollup would look
SELECT
  EXTRACT(YEAR FROM sale_date)::INT AS sale_year,
  region,
  payment_method,
  SUM(amount) AS total_amount
FROM sales
GROUP BY ROLLUP(
  EXTRACT(YEAR FROM sale_date)::INT,
  region,
  payment_method
)
ORDER BY sale_year NULLS LAST, region NULLS LAST, payment_method NULLS LAST;

This gives you subtotals at every level: year+region+method, year+region, year, and grand total.

CUBE -- All Possible Combinations

CUBE generates every possible combination of the listed columns:

CUBE(a, b)
-- is equivalent to:
GROUPING SETS (
  (a, b),
  (a),
  (b),
  ()
)

Example

SELECT
  region,
  payment_method,
  SUM(amount) AS total_amount,
  COUNT(*)    AS num_sales
FROM sales
GROUP BY CUBE(region, payment_method)
ORDER BY region NULLS LAST, payment_method NULLS LAST;

This includes everything ROLLUP gives you plus subtotals by payment_method alone (which ROLLUP omits). It is a complete cross-tabulation.

ROLLUP vs. CUBE Comparison

With two columns (region, payment_method):

With three columns, the difference grows:

• ROLLUP(a, b, c) produces 4 grouping sets.
• CUBE(a, b, c) produces 8 grouping sets (2^3).

Use ROLLUP when your columns have a natural hierarchy (year > quarter > month). Use CUBE when you want every possible subtotal.

The GROUPING() Function

The tricky part of GROUPING SETS is that subtotal rows show NULL in columns they aggregate over. But what if a column legitimately contains NULL values? The GROUPING() function resolves this ambiguity:

GROUPING(column) returns:
  0 = the column is part of the current grouping (real value, even if NULL)
  1 = the column is aggregated over (subtotal row)

Using GROUPING() to Label Rows

SELECT
  CASE WHEN GROUPING(region) = 1 THEN 'ALL REGIONS'
       ELSE region
  END AS region,
  CASE WHEN GROUPING(payment_method) = 1 THEN 'ALL METHODS'
       ELSE payment_method
  END AS payment_method,
  SUM(amount) AS total_amount,
  COUNT(*)    AS num_sales
FROM sales
GROUP BY ROLLUP(region, payment_method)
ORDER BY GROUPING(region), region, GROUPING(payment_method), payment_method;

Now instead of NULL in subtotal rows, you see clear labels:

Adding a Row Type Column

SELECT
  region,
  payment_method,
  SUM(amount) AS total_amount,
  CASE
    WHEN GROUPING(region) = 1 AND GROUPING(payment_method) = 1 THEN 'grand_total'
    WHEN GROUPING(payment_method) = 1 THEN 'region_subtotal'
    ELSE 'detail'
  END AS row_type
FROM sales
GROUP BY ROLLUP(region, payment_method)
ORDER BY region NULLS LAST, payment_method NULLS LAST;

Partial ROLLUP and CUBE

You can mix regular GROUP BY columns with ROLLUP or CUBE:

-- Always group by region, but ROLLUP on payment_method only
SELECT
  region,
  payment_method,
  SUM(amount) AS total_amount
FROM sales
GROUP BY region, ROLLUP(payment_method)
ORDER BY region, payment_method NULLS LAST;

This produces detail rows and subtotals per region, but no grand total (because region is always grouped).

Multiple ROLLUP/CUBE in One Query

SELECT
  region,
  payment_method,
  SUM(amount) AS total_amount
FROM sales
GROUP BY GROUPING SETS (
  (region, payment_method),
  (region),
  (payment_method),
  ()
)
ORDER BY
  GROUPING(region),
  region NULLS LAST,
  GROUPING(payment_method),
  payment_method NULLS LAST;

Practical Report: Monthly Sales Summary

Here is a real-world report combining ROLLUP with other techniques:

SELECT
  CASE WHEN GROUPING(region) = 1 THEN '** TOTAL **'
       ELSE region
  END AS region,
  COUNT(*)    AS num_sales,
  SUM(amount) AS revenue,
  ROUND(AVG(amount), 2) AS avg_sale,
  MIN(amount) AS min_sale,
  MAX(amount) AS max_sale,
  COUNT(DISTINCT customer_id) AS unique_customers,
  string_agg(DISTINCT payment_method, ', ' ORDER BY payment_method)
    FILTER (WHERE GROUPING(region) = 0) AS payment_methods
FROM sales
GROUP BY ROLLUP(region)
ORDER BY GROUPING(region), revenue DESC;

Performance Considerations

• GROUPING SETS, ROLLUP, and CUBE scan the table once (or a small number of times with hash aggregation), which is far more efficient than multiple UNION ALL queries.
• The planner may use HashAggregate or GroupAggregate depending on the data volume.
• Adding more columns to CUBE grows the result exponentially (2^n groupings). Be cautious with CUBE(a, b, c, d, e) -- that is 32 grouping sets.
• ROLLUP is generally more practical than CUBE because real data usually has a natural hierarchy.

Try It Yourself

Run these queries against the sales table:

1. Use ROLLUP(region) with SUM(amount) and COUNT(*) to produce subtotals by region and a grand total.

2. Use CUBE(region, payment_method) to see every combination of subtotals. How many rows are in the result?

3. Add GROUPING() calls to label subtotal rows with descriptive text like "All Regions" and "All Methods" instead of NULL.

4. Write a report using GROUPING SETS that shows totals by region, totals by payment_method, and a grand total -- but not the detail-level region+payment_method combinations.

5. Try a partial rollup: GROUP BY payment_method, ROLLUP(region). How does the output differ from ROLLUP(payment_method, region)?

Summary