ROLLUP

The ROLLUP subclause of GROUP BY generates hierarchical subtotals and a grand total in a single query. It follows an implied column hierarchy from left to right, making it the standard choice for generating year/month/day drill-down reports and financial summaries without running multiple separate queries.

ROLLUP syntax

Place ROLLUP inside the GROUP BY clause:

SELECT c1, c2, c3, aggregate(c4)
FROM table_name
GROUP BY ROLLUP (c1, c2, c3);

ROLLUP(c1, c2, c3) generates exactly 4 grouping sets following the hierarchy c1 > c2 > c3:

(c1, c2, c3)   -- detail rows
(c1, c2)       -- subtotal: drop rightmost column
(c1)           -- subtotal: drop next column
()             -- grand total

Compare this to CUBE(c1,c2,c3), which would generate all 8 combinations. ROLLUP only produces n+1 sets for n columns.

Basic ROLLUP example

Using a sales table with brand, segment, and quantity:

SELECT
  brand,
  segment,
  SUM(quantity)
FROM sales
GROUP BY ROLLUP (brand, segment)
ORDER BY brand, segment;

Output:

 brand | segment | sum
-------+---------+-----
 ABC   | Basic   | 200
 ABC   | Premium | 100
 ABC   | NULL    | 300  -- brand subtotal
 XYZ   | Basic   | 300
 XYZ   | Premium | 100
 XYZ   | NULL    | 400  -- brand subtotal
 NULL  | NULL    | 700  -- grand total

The hierarchy here is brand > segment. Reversing the order to ROLLUP(segment, brand) would instead produce segment subtotals.

ROLLUP for time-series reports

A classic use of ROLLUP is generating daily, monthly, and yearly rental counts from date data:

SELECT
  EXTRACT(YEAR  FROM rental_date) AS y,
  EXTRACT(MONTH FROM rental_date) AS m,
  EXTRACT(DAY   FROM rental_date) AS d,
  COUNT(rental_id)
FROM rental
GROUP BY ROLLUP (
  EXTRACT(YEAR  FROM rental_date),
  EXTRACT(MONTH FROM rental_date),
  EXTRACT(DAY   FROM rental_date)
)
ORDER BY y, m, d;

This produces one row per day, one subtotal per month, one subtotal per year, and a final grand total — all in a single pass.

Partial ROLLUP

A partial ROLLUP fixes certain columns and applies ROLLUP only to others:

SELECT
  segment,
  brand,
  SUM(quantity)
FROM sales
GROUP BY segment, ROLLUP (brand)
ORDER BY segment, brand;

Here segment is always present; only brand is rolled up. This produces a subtotal per segment but no grand total across all segments.

Key tips for using ROLLUP

• Column order matters: ROLLUP drops columns from right to left, so put the highest-level dimension first (e.g., year before month before day).
• Use GROUPING(col) or COALESCE(col::text, 'Total') to relabel NULL subtotal markers in report output.
• ROLLUP is more efficient than CUBE when not every cross-dimensional combination is needed — it scans less data and produces fewer result rows.
• ROLLUP produces n+1 grouping sets for n columns; plan the output size accordingly for large dimension tables.
• Combine ROLLUP with window functions (e.g., LAG) on the result to compute period-over-period changes in a second CTE step.

Reference: PostgreSQL documentation — GROUPING SETS, CUBE, and ROLLUP.