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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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.

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.

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.

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.

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 .

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.

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.

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.

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.

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.

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

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary