CUBE | PostgreSQL Tutorial

The CUBE subclause of GROUP BY generates all possible grouping set combinations for a list of columns. A single CUBE query replaces what would otherwise require multiple separate GROUP BY queries unioned together, making it ideal for multi-dimensional aggregate reporting.

CUBE syntax

The basic syntax places CUBE inside the GROUP BY clause:

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

For n columns, CUBE produces 2ⁿ grouping sets. CUBE(c1, c2, c3) is equivalent to:

GROUPING SETS (
  (c1, c2, c3),
  (c1, c2),
  (c1, c3),
  (c2, c3),
  (c1),
  (c2),
  (c3),
  ()
)

The empty grouping set () produces the grand total row. NULL in a dimension column of the result means that row aggregates across all values of that dimension.

CUBE examples

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

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

Output (NULL indicates a subtotal or grand total row):

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

Partial CUBE

A partial CUBE fixes some columns and applies CUBE only to others, reducing the number of result rows:

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

Here brand is always present in every grouping; only segment is cubed. This produces one detail row per brand+segment combination plus a subtotal row per brand — but no cross-brand subtotals.

Identifying subtotal rows with GROUPING()

Use the GROUPING() function to distinguish regular rows from subtotal rows in the result:

SELECT
  CASE WHEN GROUPING(brand) = 1 THEN 'All Brands' ELSE brand END AS brand,
  CASE WHEN GROUPING(segment) = 1 THEN 'All Segments' ELSE segment END AS segment,
  SUM(quantity) AS total_qty
FROM sales
GROUP BY CUBE (brand, segment)
ORDER BY brand, segment;

Key tips for using CUBE

NULL in a CUBE result column is a subtotal marker, not a missing value. Use GROUPING(col) or COALESCE to relabel it in reports.
The number of result rows grows exponentially: 3 columns produce up to 8 groups per combination of values. Filter or paginate large CUBE results carefully.
CUBE is evaluated in a single scan, making it more efficient than running separate GROUP BY queries and using UNION ALL to combine them.
Use a partial CUBE (GROUP BY fixed_col, CUBE(dim1, dim2)) when you only need subtotals along specific dimensions, not every possible combination.
Combine CUBE with HAVING to filter out subtotal rows below a threshold (e.g., HAVING SUM(quantity) > 100).

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

Frequently Asked Questions

What does CUBE do in PostgreSQL GROUP BY?

CUBE is a shorthand for defining multiple grouping sets at once. GROUP BY CUBE(c1, c2, c3) is equivalent to listing all 8 possible GROUPING SETS combinations: (c1,c2,c3), (c1,c2), (c1,c3), (c2,c3), (c1), (c2), (c3), and (). NULL in the result for a CUBE column indicates that row is a subtotal or grand total across all values of that column.

What is the difference between CUBE and ROLLUP in PostgreSQL?

CUBE generates all 2^n possible grouping set combinations for n columns — every subtotal and the grand total. ROLLUP generates only n+1 grouping sets following a strict hierarchy (e.g., year > month > day), producing subtotals along that hierarchy plus a grand total. Use CUBE when every combination matters (e.g., cross-dimensional reporting); use ROLLUP when there is a clear hierarchy like date drill-down.

How can I tell which rows in a CUBE result are subtotals vs detail rows?

Use the GROUPING() function. GROUPING(col) returns 1 if the column is aggregated across (i.e., the row is a subtotal or total for that dimension) and 0 if it is a regular grouped row. You can combine GROUPING() with CASE to label rows as "Detail", "Subtotal", or "Grand Total" in your output.

What is a partial CUBE in PostgreSQL?

A partial CUBE applies CUBE to only a subset of the columns while keeping the remaining columns as fixed grouping columns. For example, GROUP BY c1, CUBE(c2, c3) groups by c1 in every row and then generates all CUBE combinations for c2 and c3. This reduces the number of result rows compared to CUBE(c1, c2, c3).

When should I use CUBE instead of multiple separate GROUP BY queries?

Use CUBE when you need aggregates across all dimension combinations in a single pass — it is more efficient than running separate queries and UNION-ing them. CUBE is ideal for pivot-style reports, OLAP dashboards, and data warehouse queries where you need row counts, sums, or averages broken down by every combination of dimensions.