ROLLUP and CUBE for hierarchical totals in PostgreSQL

Introduction

ROLLUP and CUBE help you quickly get totals and subtotals for groups of data. They make it easy to see summaries at different levels.

You want to see sales totals by region, then by country within each region, and then overall total.

You need to analyze expenses by department and project, including subtotals and grand totals.

You want to create reports that show data summaries at multiple levels without writing many queries.

You want to compare combinations of categories and see all possible totals in one result.

Syntax

PostgreSQL

SELECT columns, AGGREGATE_FUNCTION(column)
FROM table
GROUP BY ROLLUP(column1, column2, ...);

-- or

SELECT columns, AGGREGATE_FUNCTION(column)
FROM table
GROUP BY CUBE(column1, column2, ...);

ROLLUP creates subtotals moving from left to right in the list of columns, ending with a grand total.

CUBE creates subtotals for all combinations of the listed columns, including the grand total.

Examples

This query shows sales totals by region, then by country within each region, and a grand total.

PostgreSQL

SELECT region, country, SUM(sales) AS total_sales
FROM sales_data
GROUP BY ROLLUP(region, country);

This query shows costs for every combination of department and project, plus subtotals and grand total.

PostgreSQL

SELECT department, project, SUM(cost) AS total_cost
FROM expenses
GROUP BY CUBE(department, project);

Sample Program

This example creates a sales table, inserts sample data, and uses ROLLUP to get totals by region, country, and grand total.

PostgreSQL

CREATE TABLE sales (
  region TEXT,
  country TEXT,
  sales INT
);

INSERT INTO sales VALUES
('North America', 'USA', 100),
('North America', 'Canada', 150),
('Europe', 'France', 200),
('Europe', 'Germany', 250);

SELECT region, country, SUM(sales) AS total_sales
FROM sales
GROUP BY ROLLUP(region, country)
ORDER BY region NULLS LAST, country NULLS LAST;

Important Notes

NULL values in the result represent subtotal or grand total rows.

Use ORDER BY with NULLS LAST to keep totals at the bottom for better readability.

ROLLUP is good for hierarchical totals; CUBE is better when you want all combinations.

Summary

ROLLUP and CUBE help create subtotals and grand totals in one query.

ROLLUP gives hierarchical totals from left to right.

CUBE gives all combinations of totals for the listed columns.