How to Use GROUPING SETS, ROLLUP, and CUBE in PostgreSQL

Aggregations are fundamental to analytical and reporting queries in PostgreSQL. While the standard GROUP BY clause is sufficient for simple summaries, real-world reporting often requires multiple levels of aggregation—such as subtotals, rollups, and grand totals—within a single result set.

Traditionally, this has been achieved using multiple GROUP BY queries combined with UNION ALL, which leads to verbose SQL, repeated table scans, and maintenance overhead.

PostgreSQL provides a robust and standards-compliant solution through GROUPING SETS, ROLLUP, and CUBE. These features allow you to compute complex aggregations efficiently in a single query while keeping SQL readable and performant.

This article explains how each feature works, when to use it, and how to interpret the results.

Let’s assume a simple sales table:

CREATE TABLE sales (
    region      TEXT,
    country     TEXT,
    product     TEXT,
    amount      NUMERIC
);

Example data:

1. GROUPING SETS

GROUPING SETS is the most flexible aggregation feature provided by PostgreSQL. It allows you to explicitly define multiple grouping combinations within a single GROUP BY clause. Instead of relying on hierarchical relationships or automatic combinations, you specify exactly which aggregations you want PostgreSQL to compute.

This approach is particularly useful in reporting scenarios where the required summaries do not follow a strict hierarchy. For example, you may need totals by region, totals by country, and a grand total—but not necessarily totals by region and country together.

Without GROUPING SETS, achieving this would require multiple GROUP BY queries combined using UNION ALL, resulting in repeated table scans and more complex SQL. GROUPING SETS solves this by performing all requested aggregations in a single pass over the data.

Syntax and Usage

SELECT
    region,
    country,
    SUM(amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS (
    (region),
    (country),
    ()
);

Each element inside GROUPING SETS represents an independent grouping level:

• (region) calculates totals per region
• (country) calculates totals per country
• () represents the grand total across the entire table

PostgreSQL evaluates all these grouping definitions together and returns a unified result set.

Output Interpretation

In the result, columns that are not part of a specific grouping set appear as NULL. These NULL values do not represent missing data; instead, they indicate that the column was aggregated at a higher level.

Use GROUPING SETS when you need precise control over aggregation levels, especially when the groupings are unrelated or non-hierarchical. It is ideal for replacing complex UNION ALL-based reporting queries with a single, efficient statement.

------|---------|-------------|
| Asia | NULL | 2200 |
| Europe | NULL | 1300 |
| NULL | India | 1500 |
| NULL | Germany | 1300 |
| NULL | NULL | 3500 |

2. ROLLUP

ROLLUP is designed for hierarchical aggregations where data naturally follows a parent–child relationship. It automatically produces subtotals and grand totals by progressively reducing the grouping columns from right to left.

This makes ROLLUP especially suitable for financial, sales, and time-based reports, such as aggregating daily sales into monthly totals and then into yearly totals.

When you specify multiple columns in a ROLLUP, PostgreSQL generates grouping levels by removing one column at a time, starting from the rightmost column.

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

Internally, PostgreSQL expands this into the following grouping sets:

• (region, country) – detailed totals
• (region) – subtotal per region
• () – grand total

Output Interpretation

Rows where the country is NULL represent regional subtotals. Rows where both region and country are NULL represent the grand total. This structured reduction makes it easy to understand and present hierarchical summaries.

Choose ROLLUP when your aggregations follow a clear hierarchy and you want PostgreSQL to generate subtotals automatically. It simplifies SQL and ensures consistent subtotal logic across reports.

------|---------|-------------|
| Asia | India | 1500 |
| Asia | Japan | 700 |
| Asia | NULL | 2200 |
| Europe | Germany | 1300 |
| Europe | NULL | 1300 |
| NULL | NULL | 3500 |

When to use ROLLUP

• Reporting with natural hierarchies
• Financial summaries (daily > monthly > yearly totals)
• Subtotals and grand totals in one result

3. CUBE

CUBE is the most powerful—and potentially most expensive—aggregation option. It generates all possible combinations of the specified grouping columns, making it suitable for multidimensional analysis similar to OLAP systems. Unlike ROLLUP, which follows a hierarchy, CUBE treats all columns as independent dimensions and computes every possible subtotal.

For the following query:

SELECT
    region,
    country,
    SUM(amount) AS total_sales
FROM sales
GROUP BY CUBE (region, country);

PostgreSQL generates the following grouping combinations:

• (region, country) – detailed totals
• (region) – totals by region
• (country) – totals by country
• () – grand total

This results in a comprehensive aggregation matrix that supports flexible slicing and dicing of data.

Output Interpretation

As with other advanced grouping features, NULL values indicate aggregated columns rather than missing data. Proper interpretation—or use of the GROUPING() function—is essential when consuming CUBE results in reports or dashboards.

Use CUBE for advanced analytical workloads, BI dashboards, and exploratory analysis where all dimension combinations are required. However, be cautious: the number of generated rows grows exponentially as you add more columns, which can impact performance and result size.

------|---------|-------------|
| Asia | India | 1500 |
| Asia | Japan | 700 |
| Europe | Germany | 1300 |
| Asia | NULL | 2200 |
| Europe | NULL | 1300 |
| NULL | India | 1500 |
| NULL | Germany | 1300 |
| NULL | NULL | 3500 |

When to use CUBE:

• Multidimensional analytics
• BI and OLAP-style reports
• When you need every subtotal combination

If you’re building reports, analytics, or dashboards on PostgreSQL—especially for ERP or BI workloads—GROUPING SETS, ROLLUP, and CUBE are must-know tools. They simplify SQL, reduce query duplication, and improve performance by letting PostgreSQL do the heavy lifting.