Advanced9 min read PostgreSQL

GROUPING SETS, ROLLUP & CUBE — Subtotals in One Pass

Produce department, division, school and grand totals from a single query — the subtotal rows executives ask for, without a stack of UNION ALLs.

GROUPING SETS
ROLLUP
CUBE
GROUPING()
subtotals
reporting

Use this when you need to:

Headcount by department with division subtotals, school subtotals and a grand total on the same page
A level × load cross-tab where every row and column also shows its margin total
Replace a four-way UNION ALL that repeats the same joins and filters with one GROUP BY
Feed a pivot-ready extract to Power BI that already contains the "All" rows the visual needs

A plain GROUP BY produces one grouping level. Reports rarely want one. ROLLUP, CUBE and GROUPING SETS ask PostgreSQL for several grouping levels in the same statement, and it scans the data once. The extra rows come back with NULL in the columns that were rolled up, and the GROUPING() function tells you which NULLs mean "subtotal" rather than "missing".

ROLLUP — a hierarchy of subtotals

ROLLUP (a, b, c) groups by (a, b, c), then (a, b), then (a), then () — the natural shape of a school → division → department hierarchy. Rows with NULL in dept_desc are division subtotals; NULL in both dept_desc and dept_division_desc are school subtotals; NULL in all three is the grand total.

SELECT
  d.dept_school_desc,
  d.dept_division_desc,
  d.dept_desc,
  COUNT(*) AS active_students
FROM ods_stu_acad_levels sal
  JOIN ods_acad_programs ap ON ap.acad_programs_id = sal.sttr_acad_program
  JOIN ods_departments d    ON d.departments_id   = ap.department_1
WHERE sal.stpr_status = 'A'
GROUP BY ROLLUP (d.dept_school_desc, d.dept_division_desc, d.dept_desc)
ORDER BY d.dept_school_desc NULLS LAST, d.dept_division_desc NULLS LAST, d.dept_desc NULLS LAST;

GROUPING() — label the subtotal rows

GROUPING(column) returns 1 when that column was rolled up on this row and 0 when it holds a real value. Use it in a CASE to print "All departments" instead of a blank, and in ORDER BY to keep totals beneath their detail rows. This is more robust than COALESCE(dept_desc, 'Total'), which would also relabel a genuinely NULL department.

SELECT
  CASE WHEN GROUPING(d.dept_school_desc) = 1 THEN 'ALL SCHOOLS'  ELSE d.dept_school_desc END AS school,
  CASE WHEN GROUPING(d.dept_desc)        = 1 THEN 'School total' ELSE d.dept_desc        END AS department,
  COUNT(*) AS active_students
FROM ods_stu_acad_levels sal
  JOIN ods_acad_programs ap ON ap.acad_programs_id = sal.sttr_acad_program
  JOIN ods_departments d    ON d.departments_id   = ap.department_1
WHERE sal.stpr_status = 'A'
GROUP BY ROLLUP (d.dept_school_desc, d.dept_desc)
ORDER BY GROUPING(d.dept_school_desc), d.dept_school_desc,
         GROUPING(d.dept_desc),        d.dept_desc;

GROUPING SETS — exactly the levels you name

When the dimensions are not a hierarchy — level and enrollment load are independent — ROLLUP would produce a level-then-load nesting you did not ask for. GROUPING SETS lists the exact groupings: by level, by load, and the empty set () for the grand total.

SELECT
  st.academic_level_desc AS level,
  st.student_load_desc   AS load,
  COUNT(*)               AS registered
FROM ods_student_terms st
WHERE st.sttr_term = '2026FA'
  AND st.sttr_current_status = 'R'
GROUP BY GROUPING SETS (
  (st.academic_level_desc),
  (st.student_load_desc),
  ()
)
ORDER BY level NULLS LAST, load NULLS LAST;

CUBE — every combination, with margins

-- Level × load cross-tab: each cell, each row total, each column total, grand total
SELECT
  COALESCE(st.academic_level_desc, 'All levels') AS level,
  COALESCE(st.student_load_desc,   'All loads')  AS load,
  COUNT(*) AS registered
FROM ods_student_terms st
WHERE st.sttr_term = '2026FA'
  AND st.sttr_current_status = 'R'
GROUP BY CUBE (st.academic_level_desc, st.student_load_desc)
ORDER BY GROUPING(st.academic_level_desc), level,
         GROUPING(st.student_load_desc),   load;

COALESCE is fine here because neither column is ever NULL in the data. When a grouping column can legitimately be NULL, use GROUPING() for the label — it is the only way to tell a subtotal row from a row whose value is genuinely unknown.

What you are replacing

-- The pre-GROUPING SETS version of the level / load / total report:
-- three scans of the same table, the same WHERE clause pasted three times.
SELECT st.academic_level_desc AS label, 'By level' AS grouping, COUNT(*) AS registered
FROM ods_student_terms st
WHERE st.sttr_term = '2026FA' AND st.sttr_current_status = 'R'
GROUP BY st.academic_level_desc
UNION ALL
SELECT st.student_load_desc, 'By load', COUNT(*)
FROM ods_student_terms st
WHERE st.sttr_term = '2026FA' AND st.sttr_current_status = 'R'
GROUP BY st.student_load_desc
UNION ALL
SELECT 'All registered', 'Total', COUNT(*)
FROM ods_student_terms st
WHERE st.sttr_term = '2026FA' AND st.sttr_current_status = 'R';

Practice challenge

Produce active-student counts by school and academic level (undergraduate / graduate), with a subtotal per school and a grand total. Label the subtotal rows 'All levels' and the grand-total row 'ALL SCHOOLS', and keep totals beneath their detail rows.

Solution

SELECT
  CASE WHEN GROUPING(d.dept_school_desc) = 1 THEN 'ALL SCHOOLS' ELSE d.dept_school_desc END AS school,
  CASE WHEN GROUPING(sal.acad_level_desc) = 1 THEN 'All levels' ELSE sal.acad_level_desc END AS level,
  COUNT(*) AS active_students
FROM ods_stu_acad_levels sal
  JOIN ods_acad_programs ap ON ap.acad_programs_id = sal.sttr_acad_program
  JOIN ods_departments d    ON d.departments_id   = ap.department_1
WHERE sal.stpr_status = 'A'
GROUP BY ROLLUP (d.dept_school_desc, sal.acad_level_desc)
ORDER BY GROUPING(d.dept_school_desc), d.dept_school_desc,
         GROUPING(sal.acad_level_desc), sal.acad_level_desc;

Practise this on real university data

Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.

Run your first query Browse practice problems

See all 21 lessons