GROUPING SETS in PostgreSQL

GROUPING SETS is a subclause of GROUP BY that lets you define multiple grouping combinations in a single SQL statement. Instead of writing separate queries and combining them with UNION ALL, you list the groupings you need and PostgreSQL computes all of them in one pass over the table.

Syntax

SELECT c1, c2, aggregate_function(c3)
FROM table_name
GROUP BY GROUPING SETS (
  (c1, c2),
  (c1),
  (c2),
  ()
);

Each entry in the GROUPING SETS list is a parenthesized set of columns. The empty set () computes the grand total. PostgreSQL produces one set of output rows per grouping set, with unparticipating columns set to NULL in the subtotal rows.

Practical Example

Create a regional product sales table:

CREATE TABLE sales_facts (
  sale_id  SERIAL PRIMARY KEY,
  region   VARCHAR NOT NULL,
  product  VARCHAR NOT NULL,
  qty      INT     NOT NULL
);

INSERT INTO sales_facts (region, product, qty) VALUES
  ('East',  'Laptop',  5),
  ('East',  'Monitor', 8),
  ('West',  'Laptop',  3),
  ('West',  'Monitor', 4),
  ('North', 'Laptop',  7),
  ('North', 'Monitor', 2);

Without GROUPING SETS, producing subtotals at three levels requires three queries plus a grand total:

-- verbose UNION ALL approach
SELECT region, product, SUM(qty) FROM sales_facts GROUP BY region, product
UNION ALL
SELECT region, NULL,    SUM(qty) FROM sales_facts GROUP BY region
UNION ALL
SELECT NULL,   product, SUM(qty) FROM sales_facts GROUP BY product
UNION ALL
SELECT NULL,   NULL,    SUM(qty) FROM sales_facts;

The GROUPING SETS equivalent is shorter and performs a single table scan:

SELECT
  region,
  product,
  SUM(qty) AS total_qty
FROM sales_facts
GROUP BY GROUPING SETS (
  (region, product),
  (region),
  (product),
  ()
)
ORDER BY region NULLS LAST, product NULLS LAST;

Sample output:

 region | product | total_qty
--------+---------+-----------
 East   | Laptop  |         5
 East   | Monitor |         8
 East   | NULL    |        13
 North  | Laptop  |         7
 North  | Monitor |         2
 North  | NULL    |         9
 West   | Laptop  |         3
 West   | Monitor |         4
 West   | NULL    |         7
 NULL   | Laptop  |        15
 NULL   | Monitor |        14
 NULL   | NULL    |        29

Rows where region or product is NULL are subtotals, not missing data.

Distinguishing subtotals from real NULLs with GROUPING()

The GROUPING() function returns 1 if the column is aggregated over in the current grouping set (i.e., it contributed to a NULL in the output) and 0 if it is genuinely grouped:

SELECT
  GROUPING(region)  AS g_region,
  GROUPING(product) AS g_product,
  region,
  product,
  SUM(qty) AS total_qty
FROM sales_facts
GROUP BY GROUPING SETS (
  (region, product),
  (region),
  (product),
  ()
)
HAVING GROUPING(region) = 0   -- only region-level rows
ORDER BY region;

Use GROUPING() in CASE expressions to replace NULL markers with descriptive labels:

SELECT
  CASE WHEN GROUPING(region)  = 1 THEN 'All Regions' ELSE region  END AS region,
  CASE WHEN GROUPING(product) = 1 THEN 'All Products' ELSE product END AS product,
  SUM(qty) AS total_qty
FROM sales_facts
GROUP BY GROUPING SETS ((region, product), (region), ())
ORDER BY 1, 2;

GROUPING SETS vs ROLLUP vs CUBE

Testing with Vela

Multi-level aggregation queries can be expensive on large fact tables. With Vela’s database branching, you can create a branch from production, run EXPLAIN ANALYZE on the GROUPING SETS query, and experiment with partial indexes on region and product before making changes to the live database. This lets you measure the real execution plan on production-representative data without any risk.

Production Tips

• Use ORDER BY region NULLS LAST, product NULLS LAST to push grand-total rows to the end of report output where subtotals are easier to read.
• Use GROUPING() instead of COALESCE to distinguish aggregation-induced NULLs from legitimate NULL values in your data.
• When every combination of dimensions is needed, CUBE is a more concise shorthand; when a hierarchy exists (year > month > day), ROLLUP is the better choice.
• Index the grouped columns for performance, especially for queries that hit large fact tables in reporting pipelines.
• For materialized view use cases, consider pre-computing only the most detailed grouping set and computing higher-level subtotals via simple aggregation on top of the materialized data.