Skip to main content
    Back

    Advanced Track

    Advanced Aggregations & Pivot/Unpivot

    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.

    What You'll Learn

    • ✓Combine several aggregation levels in one query with GROUPING SETS
    • ✓Add hierarchical subtotals and a grand total with ROLLUP
    • ✓Generate every subtotal combination with CUBE
    • ✓Tell real NULLs apart from subtotal rows using GROUPING()
    • ✓Write clean conditional aggregates with FILTER (WHERE ...)
    • ✓Pivot rows into columns (and back) with CASE-based cross-tabs

    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.

    Result — 5 rows — grand total of amount = 820:

    regionproductamount
    NorthWidget100
    NorthGadget200
    SouthWidget150
    SouthGadget250
    EastWidget120

    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.

    🧾 Real-world analogy

    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.

    Plain GROUP BY — one level

    Total amount per region: one row per region.

    Try it Yourself »
    SQL
    -- The aggregation you already know: one grouping level
-- "Total amount per region" — one row per region, nothing more.
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY region;

-- This answers ONE question. The rest of this lesson is about
-- answering several questions (region totals, product totals,
-- the grand total) in a SINGLE pass over the table.

    Result — 3 rows:

    regiontotal
    East120
    North300
    South400

    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.

    GROUPING SETS

    Region totals + product totals + grand total in one scan.

    Try it Yourself »
    SQL
    -- GROUPING SETS: list the grouping levels you want, explicitly.
-- Here: a total per region, a total per product, AND the grand total —
-- all from one scan of the table.
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
    (region),    -- subtotal per region
    (product),   -- subtotal per product
    ()           -- grand total (empty set = group everything into one row)
)
ORDER BY region, product;

-- The columns you DIDN'T group by come back as NULL on that 
...

    Result — 6 rows — 3 region totals, 2 product totals, 1 grand total:

    regionproducttotal
    NULLNULL820
    NULLGadget450
    NULLWidget370
    EastNULL120
    NorthNULL300
    SouthNULL400

    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 ().

    💡 Column order matters

    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).

    ROLLUP

    Detail rows + a subtotal per region + a grand total.

    Try it Yourself »
    SQL
    -- ROLLUP: hierarchical subtotals, rolled up right-to-left.
-- ROLLUP(region, product) is shorthand for the grouping sets:
--   (region, product), (region), ()
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY region, product;

-- Read the NULLs as "all": a NULL product means "all products in
-- this region" (a region subtotal); both NULL means the grand total.
-- Column order matters: ROLLUP(region, product) subtotals by region,
-- ROLLUP(product
...

    Result — 9 rows — 5 detail, 3 region subtotals (product = NULL), 1 grand total (both NULL):

    regionproducttotal
    NULLNULL820
    EastNULL120
    EastWidget120
    NorthNULL300
    NorthGadget200
    NorthWidget100
    SouthNULL400
    SouthGadget250
    SouthWidget150

    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.

    GROUPING() labels

    Replace subtotal NULLs with 'ALL REGIONS' / 'ALL PRODUCTS'.

    Try it Yourself »
    SQL
    -- Problem: a real NULL in the data looks identical to a "subtotal NULL".
-- GROUPING(col) returns 1 when col was rolled up on this row, else 0.
-- Use it to LABEL the subtotal and grand-total rows clearly.
SELECT
    CASE WHEN GROUPING(region)  = 1 THEN 'ALL REGIONS'  ELSE region  END AS region,
    CASE WHEN GROUPING(product) = 1 THEN 'ALL PRODUCTS' ELSE product END AS product,
    SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY GROUPING(region), region, GROUPING(pro
...

    Result — 9 rows — every subtotal now reads clearly:

    regionproducttotal
    EastWidget120
    EastALL PRODUCTS120
    NorthGadget200
    NorthWidget100
    NorthALL PRODUCTS300
    SouthGadget250
    SouthWidget150
    SouthALL PRODUCTS400
    ALL REGIONSALL PRODUCTS820

    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.

    🎯 Your Turn: ROLLUP subtotals

    Replace the ___ with the keyword that adds subtotals.

    Try it Yourself »
    SQL
    -- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal: a region report with a subtotal per region AND a grand total.

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY ___ (region, product)   -- 👉 the keyword that adds subtotals + grand total
ORDER BY region, product;

-- ✅ Expected: the detail rows, PLUS a row per region where product is
--    NULL (region subtotal), PLUS one final row where BOTH are NULL
--    (grand total = 820). 9 rows in total.

    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 explodes

    CUBE of n columns makes 2n 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.

    CUBE

    Region totals, product totals, region+product detail, and grand total.

    Try it Yourself »
    SQL
    -- CUBE: EVERY combination of the listed columns.
-- CUBE(region, product) = these four grouping sets:
--   (region, product), (region), (product), ()
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product)
ORDER BY region, product;

-- So compared with ROLLUP you ALSO get the per-product totals
-- across all regions (the (product) set). CUBE(a, b) makes 2^2 = 4
-- sets; CUBE of n columns makes 2^n sets — that grows fast.

    Result — 11 rows — ROLLUP's 9 rows PLUS the two product totals (Gadget 450, Widget 370):

    regionproducttotal
    NULLNULL820
    NULLGadget450
    NULLWidget370
    EastNULL120
    EastWidget120
    NorthNULL300
    NorthGadget200
    NorthWidget100
    SouthNULL400
    SouthGadget250
    SouthWidget150

    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.

    FILTER (WHERE …)

    Per region: all lines, big lines (amount > 150), and Widget totals.

    Try it Yourself »
    SQL
    -- FILTER (WHERE ...) runs an aggregate over only the matching rows.
-- It is the clean, modern replacement for SUM(CASE WHEN ... END).
SELECT
    region,
    COUNT(*)                                   AS lines,        -- all rows
    SUM(amount)                                AS total,        -- all amounts
    COUNT(*)    FILTER (WHERE amount > 150)     AS big_lines,    -- only amount > 150
    SUM(amount) FILTER (WHERE product = 'Widget') AS widget_total -- only Widgets
FROM sales
GROUP BY re
...

    Result — 3 rows — big_lines counts only amount > 150; widget_total sums only Widgets:

    regionlinestotalbig_lineswidget_total
    East11200120
    North23001100
    South24001150

    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.

    CASE-based pivot

    One row per region; Widget and Gadget become columns.

    Try it Yourself »
    SQL
    -- PIVOT: turn ROW values into COLUMNS using one aggregate per column.
-- Goal: one row per region, one column per product.
-- The pattern is ALWAYS: GROUP BY the row key, then
-- SUM(CASE WHEN <column value> THEN amount END) for each column.
SELECT
    region,
    SUM(CASE WHEN product = 'Widget' THEN amount END) AS widget,
    SUM(CASE WHEN product = 'Gadget' THEN amount END) AS gadget,
    SUM(amount)                                       AS total
FROM sales
GROUP BY region
ORDER BY region;


...

    Result — 3 rows — East has no Gadget sales, so that cell is NULL:

    regionwidgetgadgettotal
    East120NULL120
    North100200300
    South150250400

    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.

    🎯 Your Turn: CASE pivot

    Pivot Widget and Gadget into columns, one row per region.

    Try it Yourself »
    SQL
    -- 🎯 YOUR TURN — finish the CASE-based pivot.
-- Goal: one row per region, with Widget and Gadget as columns.

SELECT
    region,
    SUM(CASE WHEN product = 'Widget' THEN amount END) AS widget,
    SUM(CASE WHEN ___ THEN amount END)                AS gadget   -- 👉 the Gadget condition
FROM sales
GROUP BY ___          -- 👉 the column that becomes one row each
ORDER BY region;

-- ✅ Expected (3 rows):
--    East  | NULL | 120     (East has no Gadget sales)
--    North |  200 | 100     (wait — 
...

    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 25 = 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

    SyntaxPurpose
    GROUP BY GROUPING SETS ((a),(b),())Exactly the levels you list (a-totals, b-totals, grand total)
    GROUP BY ROLLUP (a, b)(a,b), (a), () — hierarchical subtotals + grand total
    GROUP BY CUBE (a, b)(a,b), (a), (b), () — every 2ⁿ combination
    GROUPING(col)Returns 1 if col is a subtotal/total row, else 0
    agg(...) FILTER (WHERE cond)Aggregate only the rows matching cond
    SUM(CASE WHEN x THEN amt END)CASE-based pivot — one expression per output column

    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.

    Q: My database errors on FILTER. What now?

    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.

    Q: Why is a pivoted cell NULL instead of 0?

    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.

    🎯 Mini-Challenge

    ROLLUP region report with a FILTER count and a GROUPING() label.

    Try it Yourself »
    SQL
    -- 🎯 MINI-CHALLENGE: A self-labelling sales summary
-- Using ONLY what this lesson covered (ROLLUP, GROUPING(), FILTER):
--   1. GROUP BY ROLLUP (region) so you get a row per region + a grand total
--   2. Show SUM(amount) AS total
--   3. Show COUNT(*) FILTER (WHERE amount > 150) AS big_lines
--   4. Replace the grand-total NULL with the text 'ALL REGIONS'
--      using CASE WHEN GROUPING(region) = 1 ...
--
-- ✅ Expected (4 rows):
--    East        | 120 | 0
--    North       | 300 | 1
--    S
...

    🎉 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 NULLs
    • ✅ 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

    Sign up for free to track which lessons you've completed and get learning reminders.

    Previous

    Cookie & Privacy Settings

    We use cookies to improve your experience, analyze traffic, and show personalized ads. You can manage your preferences below.

    By clicking "Accept All", you consent to our use of cookies for analytics and personalized advertising. You can customize your preferences or reject non-essential cookies.

    Privacy Policy • Terms of Service