Compute Every Grouping Combination at Once With CUBE

Use `GROUP BY CUBE(col_a, col_b)` to generate subtotals for every possible combination of those columns — the full power set — in a single query.

Created Apr 26, 2026 Last updated Apr 26, 2026 0/2 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Get per-region, per-category, and grand-total sales in one query

CUBE(region, category) generates four grouping sets: (region, category), (region), (category), and (). The four detail rows come from the (region, category) set: East/Clothing (200), East/Electronics (500), West/Clothing (300), West/Electronics (400). The regional subtotals come from the (region) set: East sums to 700, West to 700. The category subtotals come from the (category) set: Electronics sums to 900, Clothing to 500. The grand total row comes from the () set: 1400. ORDER BY GROUPING(region), COALESCE(region, '~~~'), GROUPING(category), COALESCE(category, '~~~') places detail rows before subtotal rows within each region, and the COALESCE replaces the grouping NULL with a high-sorting sentinel so subtotals always appear after real values. Compared to ROLLUP(region, category), which would omit the per-category subtotals, CUBE is symmetric and produces every combination.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE sales (
  region VARCHAR(10),
  category VARCHAR(15),
  amount INT
);

INSERT INTO
  sales
VALUES
  ('East', 'Electronics', 500),
  ('East', 'Clothing', 200),
  ('West', 'Electronics', 400),
  ('West', 'Clothing', 300);

Validated query Shared across supported engines.

SQL

SELECT
  region,
  category,
  SUM(amount) AS total
FROM
  sales
GROUP BY
  CUBE (region, category)
ORDER BY
  GROUPING(region),
  COALESCE(region, '~~~'),
  GROUPING(category),
  COALESCE(category, '~~~');

Expected result Returned rows for the shared example.

region	category	total
East	Clothing	200
East	Electronics	500
East	NULL	700
West	Clothing	300
West	Electronics	400
West	NULL	700
NULL	Clothing	500
NULL	Electronics	900
NULL	NULL	1400

PostgreSQL and SQL Server use identical CUBE syntax and produce identical results. MySQL, MariaDB, and SQLite do not support CUBE.

Useful when

Where this command helps.

building a cross-tabulation report that needs subtotals for every dimension independently as well as their intersection
replacing multiple separate aggregation queries with a single pass that returns all subtotal combinations

Explanation

What the command is doing.

CUBE(col_a, col_b) is shorthand for GROUPING SETS ((col_a, col_b), (col_a), (col_b), ()). With two columns it produces four grouping levels: the full cross-product, each individual column, and a grand total row. A three-column CUBE(a, b, c) produces eight levels (2^3). Columns not part of a particular grouping set appear as NULL in the output, the same as with ROLLUP and GROUPING SETS. The GROUPING(col) function returns 1 for these rollup-generated NULL values, making it easy to distinguish a subtotal placeholder from a genuine NULL in the source data. CUBE differs from ROLLUP in that ROLLUP generates a fixed hierarchical subtotal chain (e.g. by year → month → day) while CUBE is symmetric and generates every combination regardless of column order. PostgreSQL and SQL Server support CUBE with identical syntax. MySQL, MariaDB, and SQLite do not support CUBE; the same result can be built with UNION ALL of separate GROUP BY queries.

Compute Every Grouping Combination at Once With CUBE

Get per-region, per-category, and grand-total sales in one query

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.