GROUPING SETS, ROLLUP and CUBE

These extend GROUP BY to compute several levels of subtotal in one query — instead of running (and then UNION-ing together) several separate aggregate queries.

The Problem

Say you want: totals per department, totals per year, and a grand total — all in one result set. Doing this with plain GROUP BY would take three separate queries stitched together with UNION ALL.

GROUPING SETS — Full Control

Explicitly list every grouping combination you want.

SELECT department, EXTRACT(YEAR FROM hired_at) AS year, COUNT(*)
FROM employees
GROUP BY GROUPING SETS (
  (department, EXTRACT(YEAR FROM hired_at)),  -- department + year
  (department),                                -- department only
  ()                                            -- grand total
);

Rows belonging to a coarser grouping show NULL for the columns that weren't part of that grouping — e.g. the grand-total row has NULL for both department and year.

ROLLUP — Hierarchical Subtotals

ROLLUP(a, b, c) is shorthand for a specific, hierarchical sequence of grouping sets: (a, b, c), (a, b), (a), () — perfect for drill-down reports like Year → Quarter → Month.

SELECT department, EXTRACT(YEAR FROM hired_at) AS year, COUNT(*)
FROM employees
GROUP BY ROLLUP (department, EXTRACT(YEAR FROM hired_at));
-- gives: per department+year, per department, AND a grand total

CUBE — Every Possible Combination

CUBE(a, b) computes all combinations: (a, b), (a), (b), and () — useful when you need every cross-section, not just a hierarchical drill-down.

SELECT department, gender, COUNT(*)
FROM employees
GROUP BY CUBE (department, gender);
-- per department+gender, per department alone, per gender alone, AND overall

Detecting Which Level a Row Belongs To

GROUPING() returns 1 if a column was "rolled up" (not part of that row's grouping), 0 if it wasn't.

SELECT
  department, EXTRACT(YEAR FROM hired_at) AS year, COUNT(*),
  GROUPING(department) AS is_dept_total,
  GROUPING(EXTRACT(YEAR FROM hired_at)) AS is_year_total
FROM employees
GROUP BY ROLLUP (department, EXTRACT(YEAR FROM hired_at));

Pivoting Rows Into Columns

PostgreSQL has no built-in PIVOT keyword — the standard approach is conditional aggregation with FILTER (see the Advanced Aggregates lesson):

SELECT department,
  COUNT(*) FILTER (WHERE gender = 'M') AS male_count,
  COUNT(*) FILTER (WHERE gender = 'F') AS female_count
FROM employees
GROUP BY department;
-- "gender" values became columns — a manual pivot

Common Errors & Troubleshooting

• Confusing a subtotal row's NULL with a real NULL value in the data — if department itself can legitimately be NULL in your data, it becomes ambiguous whether a NULL in the result is a real value or a "this is a subtotal row" marker; use GROUPING() to tell them apart reliably.
• ROLLUP given columns in the wrong order — ROLLUP(a, b) produces subtotals for (a,b), (a), and () specifically — it is NOT the same as ROLLUP(b, a), which would roll up in the opposite order.
• Trying to PIVOT and finding no such keyword — PostgreSQL has no PIVOT statement; use COUNT(...) FILTER (WHERE ...) (or the crosstab() function from the tablefunc extension) instead.

Key Takeaways

• GROUPING SETS computes multiple aggregation levels in a single query instead of UNION-ing several queries together.
• ROLLUP is for hierarchical drill-downs (Year → Month); CUBE is for every possible cross-section.
• PostgreSQL doesn't have a PIVOT statement — use COUNT(...) FILTER (WHERE ...) per desired column instead.