Reports: ROLLUP, STRING_AGG and pivots

Part of the free LogicWiz SQL & Databases course, Module 12: CTEs and recursive queries. The queries and practice run in the app on real Postgres.

Finance wants one sheet: salary bill per department and job title, a subtotal under each department, and a grand total at the bottom. Aarav writes three queries and glues them with UNION ALL. One clause does it all.

You'll learn to

Join many values into one cell with STRING_AGG
Add subtotals and a grand total with ROLLUP, GROUPING SETS and CUBE
Tell a subtotal row from a real NULL with GROUPING()
Pivot rows into columns with FILTER or SUM(CASE ...)

Learn

STRING_AGG: a list in one cell

SELECT d.name AS department,
       COUNT(e.emp_id) AS staff,
       STRING_AGG(e.name, ', ' ORDER BY e.name) AS team
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.name
ORDER BY d.name;

STRING_AGG(value, separator) is an aggregate like SUM, but it glues text. The ORDER BY inside the brackets sorts the list; without it, the order is not guaranteed. Legal has nobody, so its team is NULL.

ROLLUP: subtotals for free

GROUP BY ROLLUP (a, b) makes three groupings in one go:

(a, b): the normal detail rows
(a): a subtotal per a, with b shown as NULL
(): the grand total, with both shown as NULL

It rolls up from right to left, like a report with subtotals under each section. CUBE (a, b) adds (b) too: every combination. GROUPING SETS ((a), (b), ()) lists exactly the groupings you want.

Payroll by department and job, with subtotals

SELECT d.name AS department, e.job_title, SUM(e.salary) AS payroll
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
WHERE d.dept_id IN (20, 30)
GROUP BY ROLLUP (d.name, e.job_title)
ORDER BY d.name NULLS LAST, e.job_title NULLS LAST;

Under each department, the row with a NULL job_title is its subtotal. The last row, NULL and NULL, is the grand total of both.

In a ROLLUP, NULL means "all". But real data has NULLs too: Manish has no department. GROUP BY ROLLUP (dept_id) then gives two rows with a NULL dept_id, his group and the grand total. Use GROUPING(col): it is 1 on rows where the column was rolled up, 0 on real groups.

GROUPING() labels the totals

SELECT CASE WHEN GROUPING(dept_id) = 1 THEN 'TOTAL'
            ELSE COALESCE(dept_id::TEXT, 'no department') END AS dept,
       COUNT(*) AS staff,
       SUM(salary) AS payroll
FROM employees
GROUP BY ROLLUP (dept_id)
ORDER BY GROUPING(dept_id), dept_id;

GROUPING(dept_id) = 1 only on the grand total row, so Manish's real NULL becomes "no department" and the total becomes "TOTAL". Sorting by GROUPING puts the total last.

GROUPING SETS: two summaries in one result

SELECT d.location, e.job_title, COUNT(*) AS staff
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY GROUPING SETS ((d.location), (e.job_title))
ORDER BY d.location NULLS LAST, e.job_title;

Head count by city, then head count by job title, in one pass. Each row fills only the column of its own grouping.

Pivot: rows into columns with FILTER

SELECT e.name,
       COALESCE(SUM(a.hours) FILTER (WHERE a.project_id = 1), 0) AS upi_checkout,
       COALESCE(SUM(a.hours) FILTER (WHERE a.project_id = 2), 0) AS app_revamp,
       SUM(a.hours) AS total
FROM assignments a
JOIN employees e ON e.emp_id = a.emp_id
WHERE e.dept_id = 10
GROUP BY e.emp_id, e.name
ORDER BY e.name;

Each FILTER (WHERE ...) makes an aggregate count only some rows, so one row per person gets one column per project. SUM(CASE WHEN a.project_id = 1 THEN a.hours END) does the same and works everywhere.

MySQL writes GROUP BY a, b WITH ROLLUP and has no GROUPING SETS or CUBE (MySQL 8.0 does add GROUPING()). It uses GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') instead of STRING_AGG, and has no FILTER: use SUM(CASE WHEN ... THEN x END) for pivots.

Interview favourites: "comma-separated list per group" (STRING_AGG / GROUP_CONCAT), "pivot monthly sales into columns" (conditional aggregation), "add a grand total row" (ROLLUP, or a UNION ALL if they want the long way), and "ROLLUP vs CUBE": ROLLUP is a hierarchy of n+1 groupings, CUBE is all 2ⁿ combinations.

In one line

STRING_AGG lists values in one cell; ROLLUP / GROUPING SETS / CUBE add subtotal and total rows (spot them with GROUPING()); FILTER or SUM(CASE) pivots rows into columns.

Interview corner

What is the difference between ROLLUP, CUBE and GROUPING SETS?

GROUPING SETS lists exactly the groupings you want. ROLLUP (a, b, c) is the hierarchy (a,b,c), (a,b), (a), (): n+1 groupings for subtotals. CUBE (a, b, c) is every combination: 2ⁿ groupings. All run in one pass instead of a UNION ALL of several GROUP BYs.

How do you get a comma-separated list of values per group?

Postgres: STRING_AGG(name, ', ' ORDER BY name). MySQL: GROUP_CONCAT(name ORDER BY name SEPARATOR ', '). Always add the ORDER BY inside, or the list order is not guaranteed.

How do you pivot rows into columns?

Conditional aggregation: group by the row key and write one aggregate per column, e.g. SUM(CASE WHEN month = 1 THEN amount END) AS jan, or in Postgres SUM(amount) FILTER (WHERE month = 1). The columns must be known when you write the query.

How do you tell a ROLLUP total row from a real NULL?

Use GROUPING(col): it returns 1 when the column was rolled up (subtotal or total row) and 0 for a real group, even when that group's value is NULL.