Advanced Aggregations

By the end of this lesson you'll build the kind of summary reports analysts live in: multiple subtotal levels and a grand total in a single query with GROUPING SETS , ROLLUP , and CUBE ; clean conditional totals with FILTER ; and cross-tab "spreadsheet" grids with a CASE -based pivot. You'll also learn the one function — GROUPING() — that stops subtotal rows from confusing you.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

Our Sample Table: sales

Every query in this lesson runs against this tiny sales table. It's deliberately small so you can verify each subtotal by hand. Three columns: the region , the product , and the amount of that sale.

Quick sums to keep handy: North = 300, South = 400, East = 120. Widget total = 370, Gadget total = 450. Everything = 820.

1. Where Plain GROUP BY Runs Out

A normal GROUP BY answers exactly one question — "total per region", say. But real reports want several answers stacked together: the per-region totals and the per-product totals and the grand total. The old way was to write three queries and glue them with UNION ALL (three passes over the table). This lesson's tools get all of that in one pass.

Think of a printed sales report. The detail lines are the individual sales; under each region there's a subtotal line; and at the very bottom a bold grand total . ROLLUP and friends are how SQL prints those subtotal and total lines for you, instead of you adding them up by hand.

2. GROUPING SETS — List the Levels You Want

GROUPING SETS lets you spell out the grouping levels you want, as a list. Each set in the list produces its own block of rows, and SQL stacks them into one result. The empty set () means "don't group by anything" — that's your grand total.

The key thing to read correctly: any column you didn't group by on a given row comes back as NULL . On a "per product" row there's no single region, so region is NULL ; on the grand-total row, everything is NULL .

Exact row order depends on your database's NULL sorting, but the six rows are always the same: three region subtotals, two product subtotals, and the 820 grand total.

3. ROLLUP — Hierarchical Subtotals

ROLLUP is the one you'll use most. It builds subtotals that "roll up" from the most detailed level to the grand total, removing one column at a time from the right. ROLLUP (region, product) is just a shortcut for the grouping sets (region, product) , (region) , and () .

ROLLUP(region, product) gives a subtotal per region (then the grand total). ROLLUP(product, region) would instead give a subtotal per product . Put the column you want to subtotal first , broadest to narrowest, e.g. ROLLUP(region, country, city) .

Notice the difference from CUBE below: ROLLUP gives you per-region subtotals but not per-product totals — there are no "all regions, Widget = 370" rows here.

4. GROUPING() — Label the Subtotal Rows

Here's the trap: a subtotal row shows NULL in the rolled-up column — but a NULL could also be a real missing value in your data. They look identical, and that quietly breaks reports.

The fix is the GROUPING() function. GROUPING(product) returns 1 when product was rolled up (a subtotal) on that row, and 0 otherwise. Wrap it in a CASE to print a friendly label instead of a bare NULL .

Your Turn: add subtotals with ROLLUP

Fill in the blank so the query returns the detail rows plus a subtotal per region plus a grand total. The expected result is in the comments so you can check yourself.

5. CUBE — Every Combination

CUBE is ROLLUP 's exhaustive sibling. It generates a subtotal for every combination of the listed columns. CUBE (region, product) produces four grouping sets: (region, product) , (region) , (product) , and () — so unlike ROLLUP , you also get the per-product totals across all regions.

CUBE of n columns makes 2 n grouping sets: 2 columns -> 4, but 5 columns -> 32. On wide tables the result set (and run time) blows up. When you only need a few specific levels, list them with GROUPING SETS instead.

6. FILTER (WHERE …) — Clean Conditional Totals

Often you want a few different aggregates that each count different rows — total lines, but also only the big ones, but also only the Widgets. The FILTER (WHERE …) clause attaches a condition to a single aggregate, so each column sees only the rows it cares about.

It does the same job as the older SUM(CASE WHEN … THEN amount END) trick, but reads far better. FILTER is supported by PostgreSQL (9.4+) and SQLite (3.30+); on other databases, fall back to the CASE form.

7. Pivot — Turn Rows into Columns

A pivot (or cross-tab) reshapes a tall list into a wide grid: instead of one row per (region, product), you get one row per region with a column for each product. Standard SQL has no universal PIVOT keyword, so the portable recipe is SUM(CASE WHEN … THEN amount END) — one such expression per output column.

Two parts make it work: the CASE keeps only the amounts for that one product (and leaves the rest NULL ), and the GROUP BY region collapses everything down to one row per region. Drop the GROUP BY and the pivot falls apart — that's the most common mistake.

Unpivot is the reverse — wide columns back to tall rows. The portable way is a UNION ALL per column: SELECT region, 'Widget' AS product, widget AS amount FROM pivoted UNION ALL SELECT region, 'Gadget', gadget FROM pivoted . You unpivot when data arrives spreadsheet-shaped and you need it normalised.

Your Turn: finish the pivot

Two blanks: the condition that selects Gadget sales, and the column to group by. Read the expected output carefully — make sure each value lands in the right column.

Common Errors (and the fix)

• Misreading the NULL subtotal rows: a NULL region in a ROLLUP result is "all regions", not missing data — but a real NULL in your column looks the same. Use GROUPING(region) = 1 to detect and label true subtotal rows; never filter them with a plain WHERE region IS NULL .
• CUBE explosion: CUBE(a, b, c, d, e) generates 2 5 = 32 grouping sets and a huge result. If you only need a handful of levels, switch to GROUPING SETS (...) and list exactly the ones you want.
• Pivot without aggregation: SELECT region, CASE WHEN product='Widget' THEN amount END ... without SUM(...) and GROUP BY region gives one row per sale , not one per region. Always wrap the CASE in an aggregate and group by the row key.
• "no such function: GROUPING" / "syntax error near FILTER": not every engine supports every feature. GROUPING SETS / ROLLUP / CUBE and FILTER need a modern engine (PostgreSQL, recent SQLite). Pick PostgreSQL in the playground, or rewrite FILTER as SUM(CASE WHEN … END) .
• Wrong subtotals from column order: ROLLUP(product, region) subtotals by product, not region. If your subtotals look "upside down", swap the column order — broadest first.

📘 Quick Reference

Syntax

Purpose

Frequently Asked Questions

Q: When should I use ROLLUP vs CUBE vs GROUPING SETS?

Use ROLLUP for a natural hierarchy (region -> country -> city) where you want subtotals down one path. Use CUBE when you genuinely need every cross-combination. Use GROUPING SETS when you want a hand-picked few levels and nothing else — it's the most precise (and efficient).

Q: How do I keep just the subtotal rows, or just the detail rows?

Filter on GROUPING() . HAVING GROUPING(product) = 1 keeps only the rows where product was rolled up (the subtotals); = 0 on every column keeps only the detail. Don't use WHERE col IS NULL — it can't tell subtotal NULLs from real ones.

Rewrite each filtered aggregate as the CASE equivalent: COUNT(*) FILTER (WHERE amount > 150) becomes COUNT(CASE WHEN amount > 150 THEN 1 END) . Same result, supported everywhere.

If no row matches the CASE condition (East had no Gadget sales), SUM over zero rows is NULL , not 0. Wrap it to show a zero: COALESCE(SUM(CASE WHEN product='Gadget' THEN amount END), 0) .

Mini-Challenge: Self-Labelling Summary

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm.

🎉 Lesson Complete

• ✅ GROUPING SETS stacks hand-picked aggregation levels into one query
• ✅ ROLLUP adds hierarchical subtotals + a grand total (order matters)
• ✅ CUBE gives every 2ⁿ combination — powerful, but it explodes on wide tables
• ✅ GROUPING() tells subtotal rows apart from real NULL s
• ✅ FILTER (WHERE …) is the clean way to aggregate a subset of rows
• ✅ A SUM(CASE WHEN …) pivot turns rows into columns — always with a GROUP BY
• ✅ Next: recursive CTEs for graph and hierarchy traversal

Practice quiz

Continue this course

• Previous: Materialized Views
• Next: Recursive CTEs

Related lessons

• GROUP BY & aggregate functions