CUBE in PostgreSQL GROUP BY

The CUBE subclause of GROUP BY generates every possible combination of grouping sets for a list of columns. A single CUBE query replaces what would otherwise require multiple separate GROUP BY queries combined with UNION ALL, making it the standard tool for multi-dimensional aggregate reporting.

Syntax

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

For n columns, CUBE produces 2^n 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 () always produces the grand total row. A NULL in a dimension column of the result means that row aggregates across all values of that dimension.

Practical Example

Create a three-dimensional fact table for event ticket sales:

CREATE TABLE ticket_sales (
  sale_id  SERIAL PRIMARY KEY,
  region   VARCHAR NOT NULL,
  venue    VARCHAR NOT NULL,
  genre    VARCHAR NOT NULL,
  tickets  INT     NOT NULL
);

INSERT INTO ticket_sales (region, venue, genre, tickets) VALUES
  ('East', 'Arena',   'Rock',     120),
  ('East', 'Arena',   'Jazz',      45),
  ('East', 'Theater', 'Jazz',      80),
  ('West', 'Arena',   'Rock',      95),
  ('West', 'Theater', 'Classical', 60),
  ('West', 'Theater', 'Jazz',      30),
  ('North','Arena',   'Rock',      70),
  ('North','Theater', 'Classical', 50);

Generate all subtotals across region and genre:

SELECT
  region,
  genre,
  SUM(tickets) AS total_tickets
FROM ticket_sales
GROUP BY CUBE (region, genre)
ORDER BY region NULLS LAST, genre NULLS LAST;

Sample output:

 region | genre     | total_tickets
--------+-----------+---------------
 East   | Jazz      |           125
 East   | Rock      |           120
 East   | NULL      |           245
 North  | Classical |            50
 North  | Rock      |            70
 North  | NULL      |           120
 West   | Classical |            60
 West   | Jazz      |            30
 West   | Rock      |            95
 West   | NULL      |           185
 NULL   | Classical |           110
 NULL   | Jazz      |           155
 NULL   | Rock      |           285
 NULL   | NULL      |           550

Using GROUPING() to label rows

Replace NULL subtotal markers with descriptive text:

SELECT
  CASE WHEN GROUPING(region) = 1 THEN 'All Regions' ELSE region END AS region,
  CASE WHEN GROUPING(genre)  = 1 THEN 'All Genres'  ELSE genre  END AS genre,
  SUM(tickets) AS total_tickets
FROM ticket_sales
GROUP BY CUBE (region, genre)
ORDER BY
  GROUPING(region),
  region,
  GROUPING(genre),
  genre;

Partial CUBE

A partial CUBE fixes one column and cubes only the others, reducing the number of output rows:

SELECT
  region,
  genre,
  SUM(tickets) AS total_tickets
FROM ticket_sales
GROUP BY region, CUBE (genre)
ORDER BY region, genre NULLS LAST;

Here region appears in every grouping; only genre is cubed. You get one detail row per region+genre combination and one subtotal per region — but no cross-region genre subtotals.

CUBE vs ROLLUP vs GROUPING SETS

Testing with Vela

Multi-dimensional CUBE queries can produce large result sets and consume significant memory on tables with many distinct dimension values. Use Vela’s database branching to run EXPLAIN ANALYZE on your CUBE query against a production-data branch before deploying it. This lets you confirm the planner’s chosen strategy and tune index coverage — for example, adding a composite index on (region, genre) — without any risk to live data.

Production Tips

• The row count grows exponentially: 3 columns produce up to 2^3 = 8 grouping sets, so the output can be large for dimensions with many distinct values. Use LIMIT, pagination, or a partial CUBE to control result size.
• NULL in a CUBE result column is a subtotal marker, not missing data. Use GROUPING(col) rather than IS NULL checks to distinguish the two cases reliably.
• CUBE scans the table once, making it more efficient than running separate GROUP BY queries and using UNION ALL.
• Combine CUBE with HAVING to filter out subtotal rows below a threshold — for example, HAVING SUM(tickets) > 50 removes low-volume combinations from the report.
• For EXPLAIN ANALYZE output, look for HashAggregate or GroupAggregate with a Grouping Sets label to confirm the planner is handling CUBE correctly.