GROUPING SETS

Introduction to PostgreSQL GROUPING SETS

GROUPING SETS is a subclause of GROUP BY that lets you compute aggregates across multiple grouping combinations in a single query. Each grouping set is a parenthesized list of columns, and PostgreSQL produces a separate subtotal for each one.

General syntax:

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

The empty set () computes the grand total across all rows.

Sample table setup

CREATE TABLE sales (
  brand    VARCHAR NOT NULL,
  segment  VARCHAR NOT NULL,
  quantity INT     NOT NULL,
  PRIMARY KEY (brand, segment)
);

INSERT INTO sales (brand, segment, quantity)
VALUES
  ('ABC', 'Premium', 100),
  ('ABC', 'Basic',   200),
  ('XYZ', 'Premium', 100),
  ('XYZ', 'Basic',   300);

Why GROUPING SETS replaces UNION ALL

Without GROUPING SETS, getting subtotals at four levels requires four separate queries joined with UNION ALL:

SELECT brand, segment, SUM(quantity) FROM sales GROUP BY brand, segment
UNION ALL
SELECT brand, NULL,    SUM(quantity) FROM sales GROUP BY brand
UNION ALL
SELECT NULL,  segment, SUM(quantity) FROM sales GROUP BY segment
UNION ALL
SELECT NULL,  NULL,    SUM(quantity) FROM sales;

This is verbose and scans the sales table four times. The GROUPING SETS equivalent is more concise and performs a single scan:

SELECT brand, segment, SUM(quantity)
FROM sales
GROUP BY GROUPING SETS (
  (brand, segment),
  (brand),
  (segment),
  ()
);

Output:

 brand | segment | sum
-------+---------+-----
 null  | null    | 700
 XYZ   | Basic   | 300
 ABC   | Premium | 100
 ABC   | Basic   | 200
 XYZ   | Premium | 100
 ABC   | null    | 300
 XYZ   | null    | 400
 null  | Basic   | 500
 null  | Premium | 200
(9 rows)

Rows where brand or segment is NULL represent subtotals for that level of aggregation.

Using the GROUPING() function

GROUPING(column) returns 0 if the column is part of the current grouping set and 1 if it is not. This lets you distinguish aggregation-introduced NULLs from real NULL data:

SELECT
  GROUPING(brand)   AS grouping_brand,
  GROUPING(segment) AS grouping_segment,
  brand,
  segment,
  SUM(quantity)
FROM sales
GROUP BY GROUPING SETS (
  (brand),
  (segment),
  ()
)
ORDER BY brand, segment;

Filter to only brand-level subtotals using GROUPING() in the HAVING clause:

SELECT
  GROUPING(brand)   AS grouping_brand,
  GROUPING(segment) AS grouping_segment,
  brand,
  segment,
  SUM(quantity)
FROM sales
GROUP BY GROUPING SETS (
  (brand),
  (segment),
  ()
)
HAVING GROUPING(brand) = 0
ORDER BY brand, segment;

GROUPING SETS tips

• Use GROUPING() to distinguish NULLs introduced by the grouping operation from NULLs that exist in the actual data.
• Include the empty set () in your grouping list to add a grand total row without a separate query.
• When all combinations of columns are needed, consider CUBE as a shorthand — CUBE (brand, segment) is equivalent to GROUPING SETS ((brand, segment), (brand), (segment), ()).
• For hierarchical subtotals (e.g., year → month → day), ROLLUP is a more concise alternative to a manually written GROUPING SETS.
• Order results with ORDER BY brand NULLS LAST, segment NULLS LAST to push grand-total rows to the bottom of the output.