ROLLUP in PostgreSQL GROUP BY

The ROLLUP subclause of GROUP BY generates a hierarchy of subtotals and a grand total in a single query. It progressively drops columns from right to left, producing one subtotal level per removed column. ROLLUP is the standard choice for date drill-down reports (year → quarter → month → day) and financial summaries where a clear hierarchy exists.

Syntax

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: c3 rolled up
(c1)           -- subtotal: c2 and c3 rolled up
()             -- grand total

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

Practical Example

Create a quarterly revenue table and explore ROLLUP for hierarchical reports:

CREATE TABLE quarterly_revenue (
  rev_id   SERIAL PRIMARY KEY,
  yr       INT     NOT NULL,
  quarter  INT     NOT NULL CHECK (quarter BETWEEN 1 AND 4),
  region   VARCHAR NOT NULL,
  revenue  NUMERIC(12, 2) NOT NULL
);

INSERT INTO quarterly_revenue (yr, quarter, region, revenue) VALUES
  (2024, 1, 'East',  45000.00),
  (2024, 1, 'West',  38000.00),
  (2024, 2, 'East',  52000.00),
  (2024, 2, 'West',  41000.00),
  (2024, 3, 'East',  61000.00),
  (2024, 3, 'West',  47000.00),
  (2024, 4, 'East',  58000.00),
  (2024, 4, 'West',  53000.00),
  (2025, 1, 'East',  49000.00),
  (2025, 1, 'West',  42000.00);

Generate yearly totals, quarterly totals, and a grand total in one query:

SELECT
  yr,
  quarter,
  SUM(revenue) AS total_revenue
FROM quarterly_revenue
GROUP BY ROLLUP (yr, quarter)
ORDER BY yr NULLS LAST, quarter NULLS LAST;

Sample output:

  yr  | quarter | total_revenue
------+---------+---------------
 2024 |       1 |      83000.00
 2024 |       2 |      93000.00
 2024 |       3 |     108000.00
 2024 |       4 |     111000.00
 2024 |    NULL |     395000.00  -- yearly subtotal
 2025 |       1 |      91000.00
 2025 |    NULL |      91000.00  -- yearly subtotal
 NULL |    NULL |     486000.00  -- grand total

The three-level hierarchy (year → quarter → all) is produced from a single scan of the table.

ROLLUP for time-series with date functions

Use date_trunc or EXTRACT to drill down to day, month, and year:

SELECT
  EXTRACT(YEAR  FROM sale_date)  AS yr,
  EXTRACT(MONTH FROM sale_date)  AS mo,
  EXTRACT(DAY   FROM sale_date)  AS dy,
  COUNT(*)                       AS sale_count
FROM some_sales_table
GROUP BY ROLLUP (
  EXTRACT(YEAR  FROM sale_date),
  EXTRACT(MONTH FROM sale_date),
  EXTRACT(DAY   FROM sale_date)
)
ORDER BY yr NULLS LAST, mo NULLS LAST, dy NULLS LAST;

Partial ROLLUP

A partial ROLLUP fixes a column and applies ROLLUP only to the remaining columns:

SELECT
  region,
  yr,
  SUM(revenue) AS total_revenue
FROM quarterly_revenue
GROUP BY region, ROLLUP (yr)
ORDER BY region, yr NULLS LAST;

region appears in every grouping; yr is rolled up. This produces a subtotal per region but no grand total across all regions.

Labeling subtotal rows

Use GROUPING() or COALESCE to replace NULL subtotal markers with readable labels:

SELECT
  COALESCE(yr::TEXT, 'All Years')        AS year_label,
  COALESCE(quarter::TEXT, 'All Quarters') AS quarter_label,
  SUM(revenue)                            AS total_revenue
FROM quarterly_revenue
GROUP BY ROLLUP (yr, quarter)
ORDER BY yr NULLS LAST, quarter NULLS LAST;

ROLLUP vs CUBE vs GROUPING SETS

Testing with Vela

Year-end or quarter-close reports that use ROLLUP can be long-running on large fact tables. Vela’s database branching lets you benchmark the ROLLUP query against a production-data branch, try different index configurations (e.g., a composite index on (yr, quarter) vs. separate single-column indexes), and verify results — all without touching the live database.

Production Tips

• Column order matters in ROLLUP: always put the highest-level dimension first (year before month, region before city). Reversing the order changes which subtotals are produced.
• Use GROUPING(col) or COALESCE(col::text, 'Total') to relabel NULL subtotal markers in reports rather than leaving them as raw NULL.
• ROLLUP is more efficient than CUBE when not every cross-dimensional combination is needed — it produces far fewer result rows.
• Combine ROLLUP output with window functions like LAG in a subsequent CTE to compute period-over-period changes without an extra join.
• When using EXPLAIN ANALYZE to debug ROLLUP performance, look for GroupAggregate or HashAggregate nodes with a Grouping Sets: rollup(...) annotation to confirm the planner is processing it correctly.