Picture this: your VP of Sales drops a request on your desk at 4 PM on a Friday. She needs a report that shows total revenue broken down by region, then by region and product category, then by product category alone, and finally a company-wide grand total — all in a single result set that can feed into an executive dashboard. Your first instinct might be to write four separate queries and UNION them together. That works, but it's brittle, verbose, and slow. Every time someone adds a new dimension to the report, you're rewriting significant chunks of SQL.
This is exactly the problem that ROLLUP, CUBE, and GROUPING SETS were designed to solve. These extensions to the GROUP BY clause let you compute multiple levels of aggregation in a single query pass, producing the subtotals, cross-dimensional totals, and grand totals that executive reports demand. They're part of the SQL standard and supported in PostgreSQL, SQL Server, MySQL 8+, BigQuery, Snowflake, and most other major databases.
By the end of this lesson, you'll understand not just the syntax but the logic behind each operator — when to reach for which one, how to handle the NULLs they introduce, how to nest and combine them, and how they perform at scale. You'll build a complete multi-level sales summary report from scratch.
What you'll learn:
ROLLUP produces hierarchical subtotals along a defined dimension chainCUBE generates every possible combination of subtotals across multiple dimensionsGROUPING SETS gives you precise, surgical control over which aggregation levels appearGROUPING() and GROUPING_ID() to distinguish real NULLs from aggregation NULLsYou should be comfortable with standard GROUP BY aggregations, window functions, and CTEs. You don't need to have used ROLLUP or CUBE before, but you should understand what SUM(), COUNT(), and AVG() do in a grouped context. Basic familiarity with CASE expressions will help in the GROUPING() section.
Throughout this lesson we'll work with a sales dataset that's realistic enough to be interesting. Let's create it:
-- Create the sales table
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
sale_date DATE,
region VARCHAR(50),
country VARCHAR(50),
product_line VARCHAR(50),
product_name VARCHAR(100),
channel VARCHAR(50),
units_sold INT,
unit_price NUMERIC(10,2),
unit_cost NUMERIC(10,2)
);
-- Insert representative data
INSERT INTO sales VALUES
(1, '2024-01-15', 'North America', 'USA', 'Hardware', 'Laptop Pro', 'Online', 12, 1299.00, 780.00),
(2, '2024-01-18', 'North America', 'Canada', 'Hardware', 'Laptop Pro', 'Retail', 5, 1299.00, 780.00),
(3, '2024-01-22', 'North America', 'USA', 'Software', 'Analytics Suite','Online', 30, 299.00, 45.00),
(4, '2024-02-03', 'Europe', 'Germany', 'Hardware', 'Laptop Pro', 'Retail', 8, 1350.00, 780.00),
(5, '2024-02-07', 'Europe', 'France', 'Software', 'Analytics Suite','Online', 22, 299.00, 45.00),
(6, '2024-02-14', 'Europe', 'Germany', 'Software', 'Dev Tools', 'Online', 18, 199.00, 30.00),
(7, '2024-03-01', 'APAC', 'Japan', 'Hardware', 'Tablet X', 'Online', 25, 649.00, 310.00),
(8, '2024-03-05', 'APAC', 'Australia','Software','Analytics Suite','Retail', 10, 299.00, 45.00),
(9, '2024-03-12', 'APAC', 'Japan', 'Hardware', 'Laptop Pro', 'Online', 6, 1299.00, 780.00),
(10, '2024-04-02', 'North America', 'USA', 'Hardware', 'Tablet X', 'Retail', 20, 649.00, 310.00),
(11, '2024-04-09', 'North America', 'Canada', 'Software', 'Dev Tools', 'Online', 35, 199.00, 30.00),
(12, '2024-04-15', 'Europe', 'France', 'Hardware', 'Tablet X', 'Online', 14, 649.00, 310.00),
(13, '2024-05-01', 'APAC', 'Australia','Hardware','Tablet X', 'Online', 18, 649.00, 310.00),
(14, '2024-05-10', 'North America', 'USA', 'Software', 'Dev Tools', 'Online', 50, 199.00, 30.00),
(15, '2024-05-22', 'Europe', 'Germany', 'Hardware', 'Tablet X', 'Retail', 11, 649.00, 310.00);
-- A computed revenue column will be handy
-- revenue = units_sold * unit_price
-- margin = units_sold * (unit_price - unit_cost)
This gives us three regions, multiple countries, two product lines, three products, and two channels — enough dimensionality to make the aggregation operators genuinely useful.
ROLLUP is the right tool when your dimensions have a natural hierarchy. Region contains countries. Year contains quarters contains months. Product line contains products. ROLLUP(a, b, c) produces aggregations for:
(a, b, c) — the full detail level(a, b) — subtotal collapsing c(a) — subtotal collapsing b and c() — the grand totalThat's n + 1 grouping levels for n columns. The order of columns in ROLLUP determines the hierarchy, which is why it matters deeply.
Let's start with a simple two-level rollup: region and product line.
SELECT
region,
product_line,
SUM(units_sold * unit_price) AS total_revenue,
SUM(units_sold * (unit_price - unit_cost)) AS total_margin
FROM sales
GROUP BY ROLLUP(region, product_line)
ORDER BY region NULLS LAST, product_line NULLS LAST;
The result looks like this (abbreviated):
region | product_line | total_revenue | total_margin
----------------|--------------|---------------|-------------
APAC | Hardware | 51,769.00 | 22,869.00
APAC | Software | 5,980.00 | 5,030.00
APAC | NULL | 57,749.00 | 27,899.00 ← APAC subtotal
Europe | Hardware | 51,006.00 | 21,146.00
Europe | Software | 9,956.00 | 8,516.00
Europe | NULL | 60,962.00 | 29,662.00 ← Europe subtotal
North America | Hardware | 46,927.00 | 23,497.00
North America | Software | 25,547.00 | 22,127.00
North America | NULL | 72,474.00 | 45,624.00 ← NA subtotal
NULL | NULL | 191,185.00 | 103,185.00 ← Grand total
Notice that ROLLUP introduces NULLs in the grouping columns to represent "this level has been collapsed." That last row — where both region and product_line are NULL — is the grand total.
Now let's use the full hierarchy:
SELECT
region,
country,
product_line,
SUM(units_sold * unit_price) AS total_revenue,
COUNT(DISTINCT sale_id) AS transaction_count
FROM sales
GROUP BY ROLLUP(region, country, product_line)
ORDER BY
region NULLS LAST,
country NULLS LAST,
product_line NULLS LAST;
This produces four levels:
(region, country, product_line) — individual combinations(region, country) — per-country subtotals(region) — per-region subtotals() — grand totalKey insight:
ROLLUPis directional.ROLLUP(region, country)is NOT the same asROLLUP(country, region). The first gives you region-level subtotals; the second gives you country-level subtotals. Always order your columns from highest to lowest in the hierarchy.
Sometimes you want to fix one dimension and roll up across others. This is where ROLLUP inside a GROUP BY alongside regular columns becomes useful:
-- Keep channel fixed; roll up within region/product_line
SELECT
channel,
region,
product_line,
SUM(units_sold * unit_price) AS total_revenue
FROM sales
GROUP BY channel, ROLLUP(region, product_line)
ORDER BY channel, region NULLS LAST, product_line NULLS LAST;
Here, channel is always present in every row. The rollup only applies to region and product_line. You'll get per-channel grand totals and per-channel, per-region subtotals — but never a cross-channel total.
ROLLUP assumes a hierarchy. CUBE assumes nothing — it generates subtotals for every possible subset of the grouping columns. For n columns, CUBE generates 2^n grouping combinations.
With three columns (region, product_line, channel), CUBE produces:
(region, product_line, channel) — full detail(region, product_line) — by region and product line(region, channel) — by region and channel(product_line, channel) — by product line and channel(region) — by region alone(product_line) — by product line alone(channel) — by channel alone() — grand totalThat's 8 grouping sets from 3 columns. At 4 columns it's 16. At 5 it's 32. This is both CUBE's superpower and its danger.
SELECT
region,
product_line,
channel,
SUM(units_sold * unit_price) AS total_revenue,
SUM(units_sold * (unit_price - unit_cost)) AS total_margin,
COUNT(*) AS row_count
FROM sales
GROUP BY CUBE(region, product_line, channel)
ORDER BY
region NULLS LAST,
product_line NULLS LAST,
channel NULLS LAST;
The output will have significantly more rows than a simple GROUP BY. You'll see rows like:
region | product_line | channel | total_revenue | ...
--------|--------------|---------|---------------|----
APAC | Hardware | Online | 37,819.00 | ← (region, product_line, channel)
APAC | Hardware | Retail | 11,682.00 |
APAC | Hardware | NULL | 49,501.00 | ← (region, product_line) subtotal
APAC | NULL | Online | 43,799.00 | ← (region, channel) subtotal
APAC | NULL | Retail | 13,950.00 |
APAC | NULL | NULL | 57,749.00 | ← (region) subtotal
...
NULL | Hardware | NULL | 149,702.00 | ← (product_line) subtotal
NULL | Software | NULL | 41,483.00 |
NULL | NULL | Online | 148,575.00 | ← (channel) subtotal
NULL | NULL | Retail | 42,610.00 |
NULL | NULL | NULL | 191,185.00 | ← grand total
Warning: Be careful with
CUBEon high-cardinality columns. ACUBEover 6 columns produces 64 grouping combinations — and if each grouping combination itself has many distinct values, your result set explodes.CUBEis most appropriate when you have a small number of low-cardinality dimensions.
Use ROLLUP when your dimensions are inherently hierarchical and you only need to roll up in one direction (geographic hierarchy, date hierarchy, organizational hierarchy).
Use CUBE when your report consumers need to slice data along any axis — when a product manager might filter by region, a finance person might filter by channel, and a category manager might filter by product line, and all of them need subtotals within their chosen dimension.
GROUPING SETS is the most flexible of the three. Instead of following a formula (like ROLLUP's n+1 levels or CUBE's 2^n levels), you explicitly list exactly which groupings you want. It's the SQL equivalent of specifying exactly which UNION ALL branches you need — but executed as a single scan.
The syntax looks like this:
GROUP BY GROUPING SETS (
(dim1, dim2), -- grouping combination 1
(dim1), -- grouping combination 2
() -- grand total (empty parens)
)
Here's a classic case: you need (a) totals by region and product line, (b) totals by region alone, and (c) a grand total. The UNION ALL version:
-- The verbose, fragile way
SELECT region, product_line, SUM(units_sold * unit_price) AS revenue
FROM sales
GROUP BY region, product_line
UNION ALL
SELECT region, NULL, SUM(units_sold * unit_price)
FROM sales
GROUP BY region
UNION ALL
SELECT NULL, NULL, SUM(units_sold * unit_price)
FROM sales;
This scans the table three times. Now with GROUPING SETS:
-- The elegant, single-scan way
SELECT
region,
product_line,
SUM(units_sold * unit_price) AS total_revenue
FROM sales
GROUP BY GROUPING SETS (
(region, product_line),
(region),
()
)
ORDER BY region NULLS LAST, product_line NULLS LAST;
Same result, single table scan, and adding or removing a grouping level is a one-line change.
Imagine your VP needs a report that shows:
Notice that levels 1, 2, and 3 are not in a hierarchy with each other. ROLLUP and CUBE can't express this cleanly. GROUPING SETS can:
SELECT
region,
product_line,
channel,
SUM(units_sold * unit_price) AS total_revenue,
SUM(units_sold * (unit_price - unit_cost)) AS total_margin,
COUNT(DISTINCT sale_id) AS transactions
FROM sales
GROUP BY GROUPING SETS (
(region, product_line), -- main breakdown table
(channel), -- sidebar widget
(product_line), -- comparison chart
() -- KPI grand total
)
ORDER BY
GROUPING(region, product_line, channel),
region NULLS LAST,
product_line NULLS LAST,
channel NULLS LAST;
This single query produces all four output shapes that would normally require four separate queries or a complex UNION ALL chain.
Here's the nasty problem hiding in all three operators: they use NULL to mean "this column was collapsed for aggregation," but your data might also legitimately contain NULLs. If country can be NULL in your sales table (say, for online-only transactions that weren't geographically tagged), how do you tell a real NULL from a rollup NULL?
The GROUPING() function solves this. It returns 1 if the column was collapsed (aggregation NULL) and 0 if the column is part of the actual grouping (real value or real NULL).
SELECT
GROUPING(region) AS is_region_rolled_up,
GROUPING(product_line) AS is_product_rolled_up,
region,
product_line,
SUM(units_sold * unit_price) AS total_revenue
FROM sales
GROUP BY ROLLUP(region, product_line)
ORDER BY region NULLS LAST, product_line NULLS LAST;
Output:
is_region_rolled_up | is_product_rolled_up | region | product_line | total_revenue
--------------------|----------------------|---------|--------------|---------------
0 | 0 | APAC | Hardware | 51,769.00
0 | 0 | APAC | Software | 5,980.00
0 | 1 | APAC | NULL | 57,749.00 ← APAC subtotal
...
1 | 1 | NULL | NULL | 191,185.00 ← grand total
The GROUPING() function shines when you use it inside a CASE expression to create human-readable row labels:
SELECT
CASE
WHEN GROUPING(region) = 1 THEN '** GRAND TOTAL **'
WHEN GROUPING(product_line) = 1 THEN region || ' — Subtotal'
ELSE region
END AS region_label,
CASE
WHEN GROUPING(product_line) = 1 THEN '(All Products)'
ELSE product_line
END AS product_label,
SUM(units_sold * unit_price) AS total_revenue,
SUM(units_sold * (unit_price - unit_cost)) AS total_margin
FROM sales
GROUP BY ROLLUP(region, product_line)
ORDER BY region NULLS LAST, product_line NULLS LAST;
Now your report output is immediately readable without consumers needing to know that NULL means "subtotal":
region_label | product_label | total_revenue | total_margin
-------------------------|----------------|---------------|-------------
APAC | Hardware | 51,769.00 | 22,869.00
APAC | Software | 5,980.00 | 5,030.00
APAC — Subtotal | (All Products) | 57,749.00 | 27,899.00
Europe | Hardware | 51,006.00 | 21,146.00
Europe | Software | 9,956.00 | 8,516.00
Europe — Subtotal | (All Products) | 60,962.00 | 29,662.00
North America | Hardware | 46,927.00 | 23,497.00
North America | Software | 25,547.00 | 22,127.00
North America — Subtotal | (All Products) | 72,474.00 | 45,624.00
** GRAND TOTAL ** | (All Products) | 191,185.00 | 103,185.00
GROUPING_ID() takes multiple columns and returns a single integer that represents the bitmask of which columns are rolled up. This is useful for programmatic filtering and ordering.
SELECT
GROUPING_ID(region, product_line, channel) AS grouping_level,
region,
product_line,
channel,
SUM(units_sold * unit_price) AS total_revenue
FROM sales
GROUP BY CUBE(region, product_line, channel)
ORDER BY grouping_level, region NULLS LAST, product_line NULLS LAST, channel NULLS LAST;
GROUPING_ID returns:
0 when all columns are present (full detail)1 when only channel is rolled up2 when only product_line is rolled up3 when both product_line and channel are rolled up4 when only region is rolled up7 (grand total, all rolled up)Tip: Use
GROUPING_ID()as anORDER BYkey to ensure detail rows always appear before subtotals before grand totals, regardless of the data values in those rows.
You can mix and nest these operators in the same GROUP BY clause to express complex reporting requirements precisely.
Multiple GROUPING SETS lists in the same GROUP BY clause are equivalent to their cross product:
-- This:
GROUP BY GROUPING SETS (a, b), GROUPING SETS (c, d)
-- Is equivalent to:
GROUP BY GROUPING SETS (
(a, c), (a, d),
(b, c), (b, d)
)
This is more of a theoretical note than a common pattern, but it explains how the database internally interprets mixed GROUP BY clauses.
Understanding that ROLLUP and CUBE are syntactic sugar over GROUPING SETS is genuinely useful for debugging. Here are the equivalencies:
-- ROLLUP(a, b, c) is equivalent to:
GROUPING SETS (
(a, b, c),
(a, b),
(a),
()
)
-- CUBE(a, b) is equivalent to:
GROUPING SETS (
(a, b),
(a),
(b),
()
)
When a query is producing unexpected results, translating your ROLLUP or CUBE into explicit GROUPING SETS and checking each set in isolation is an excellent debugging strategy.
You can combine regular GROUP BY columns with CUBE or ROLLUP:
-- Fix year, cube over region and product_line
SELECT
EXTRACT(YEAR FROM sale_date) AS sale_year,
region,
product_line,
SUM(units_sold * unit_price) AS total_revenue
FROM sales
GROUP BY EXTRACT(YEAR FROM sale_date), CUBE(region, product_line)
ORDER BY sale_year, region NULLS LAST, product_line NULLS LAST;
This gives you a CUBE of region × product_line within each year, but never a cross-year total. Exactly what you want when year is a filter, not an aggregation target.
These operators aren't magic — they still require the database to compute multiple aggregation levels. Here's what to understand before deploying them in production.
The key performance advantage of ROLLUP, CUBE, and GROUPING SETS over UNION ALL is that most query engines can compute them in a single pass over the data (or at least reduce the number of passes dramatically compared to separate queries). The data is read once, sorted or hashed once, and then aggregations are rolled up from the most granular level.
However, for CUBE with many dimensions, some engines will internally execute multiple group-by operations and merge them. Check your execution plan.
-- In PostgreSQL, check how the engine handles it:
EXPLAIN (ANALYZE, BUFFERS)
SELECT region, product_line, SUM(units_sold * unit_price)
FROM sales
GROUP BY CUBE(region, product_line);
Look for MixedAggregate or HashAggregate nodes. PostgreSQL's MixedAggregate node handles multiple grouping sets in a single pass and is generally very efficient.
For large tables, the scan cost dominates. A covering index on the grouping columns plus the measure columns can dramatically reduce IO:
-- An index that supports region/product_line rollups
CREATE INDEX idx_sales_region_product
ON sales(region, product_line)
INCLUDE (units_sold, unit_price, unit_cost);
For very large datasets (hundreds of millions of rows), computing a CUBE at query time may be prohibitive. In that case, consider materializing the aggregation results into a summary table using a scheduled job, and applying GROUPING SETS over the pre-aggregated data rather than raw transactions.
Let's put everything together. You'll build a complete multi-level sales summary that a real dashboard could consume. The report needs to support:
That's a ROLLUP(region, product_line, channel) — but we also want to add a cross-dimensional view: totals by product line regardless of region (which ROLLUP doesn't provide). So we'll use GROUPING SETS that mirrors a ROLLUP but adds an extra set.
Part 1: Build the core aggregation
WITH sales_summary AS (
SELECT
region,
product_line,
channel,
GROUPING(region) AS g_region,
GROUPING(product_line) AS g_product,
GROUPING(channel) AS g_channel,
GROUPING_ID(region, product_line, channel) AS grouping_level,
SUM(units_sold * unit_price) AS total_revenue,
SUM(units_sold * (unit_price - unit_cost)) AS total_margin,
SUM(units_sold) AS total_units,
COUNT(DISTINCT sale_id) AS transactions
FROM sales
GROUP BY GROUPING SETS (
(region, product_line, channel), -- detail level
(region, product_line), -- region + product subtotals
(region), -- region subtotals
(product_line), -- product-only cross-regional view
() -- grand total
)
)
SELECT
grouping_level,
CASE
WHEN g_region = 1 AND g_product = 1 AND g_channel = 1
THEN '★ GRAND TOTAL'
WHEN g_region = 0 AND g_product = 1 AND g_channel = 1
THEN '▶ ' || region || ' [Region Total]'
WHEN g_region = 1 AND g_product = 0 AND g_channel = 1
THEN '◆ ' || product_line || ' [All Regions]'
WHEN g_region = 0 AND g_product = 0 AND g_channel = 1
THEN ' ' || region || ' / ' || product_line || ' [Subtotal]'
ELSE ' ' || region || ' / ' || product_line || ' / ' || channel
END AS report_label,
total_revenue,
total_margin,
ROUND(total_margin::NUMERIC / NULLIF(total_revenue, 0) * 100, 1) AS margin_pct,
total_units,
transactions
FROM sales_summary
ORDER BY
grouping_level DESC, -- grand total first, then less rolled-up levels
region NULLS LAST,
product_line NULLS LAST,
channel NULLS LAST;
Part 2: Add a running share of grand total
Extend the CTE to include each row's percentage of the grand total revenue:
WITH sales_summary AS (
-- (same as above)
SELECT
region, product_line, channel,
GROUPING(region) AS g_region,
GROUPING(product_line) AS g_product,
GROUPING(channel) AS g_channel,
GROUPING_ID(region, product_line, channel) AS grouping_level,
SUM(units_sold * unit_price) AS total_revenue,
SUM(units_sold * (unit_price - unit_cost)) AS total_margin,
SUM(units_sold) AS total_units,
COUNT(DISTINCT sale_id) AS transactions
FROM sales
GROUP BY GROUPING SETS (
(region, product_line, channel),
(region, product_line),
(region),
(product_line),
()
)
),
grand_total AS (
SELECT total_revenue AS gt_revenue
FROM sales_summary
WHERE grouping_level = 7 -- all dimensions rolled up
)
SELECT
s.grouping_level,
CASE
WHEN s.g_region = 1 AND s.g_product = 1 AND s.g_channel = 1
THEN '★ GRAND TOTAL'
WHEN s.g_region = 0 AND s.g_product = 1 AND s.g_channel = 1
THEN '▶ ' || s.region || ' [Region Total]'
WHEN s.g_region = 1 AND s.g_product = 0 AND s.g_channel = 1
THEN '◆ ' || s.product_line || ' [All Regions]'
WHEN s.g_region = 0 AND s.g_product = 0 AND s.g_channel = 1
THEN ' ' || s.region || ' / ' || s.product_line || ' [Subtotal]'
ELSE ' ' || s.region || ' / ' || s.product_line || ' / ' || s.channel
END AS report_label,
s.total_revenue,
ROUND(s.total_revenue / g.gt_revenue * 100, 1) AS pct_of_total,
s.total_margin,
ROUND(s.total_margin::NUMERIC / NULLIF(s.total_revenue, 0) * 100, 1) AS margin_pct,
s.total_units,
s.transactions
FROM sales_summary s
CROSS JOIN grand_total g
ORDER BY
s.grouping_level DESC,
s.region NULLS LAST,
s.product_line NULLS LAST,
s.channel NULLS LAST;
This gives you a fully labeled, hierarchical report with percentage-of-total and margin columns — exactly what you'd feed into a BI tool or export to a stakeholder.
This is the most common error. When you see a NULL in a ROLLUP or CUBE result, your first instinct might be to filter it out with WHERE region IS NOT NULL. That drops all your subtotals. Always use GROUPING() to distinguish rollup NULLs from data NULLs before applying filters.
-- WRONG: This removes all subtotal rows
WHERE region IS NOT NULL
-- RIGHT: This removes only true missing data, keeps subtotals
WHERE GROUPING(region) = 1 OR region IS NOT NULL
If you include a column in SELECT that's not in any of your GROUPING SETS and not in an aggregate function, some databases will throw an error, others will return arbitrary values. Be explicit:
-- WRONG: sale_date is not in any grouping set or aggregate
SELECT region, sale_date, SUM(units_sold * unit_price)
FROM sales
GROUP BY ROLLUP(region)
-- RIGHT: Either group it, aggregate it, or remove it
SELECT region, MAX(sale_date), SUM(units_sold * unit_price)
FROM sales
GROUP BY ROLLUP(region)
The default ORDER BY behavior for NULLs varies by database. In PostgreSQL, NULLs sort last in ascending order by default (NULLS LAST). In SQL Server, NULLs sort first. If your report needs grand totals at the bottom and detail at the top (or vice versa), use NULLS LAST / NULLS FIRST explicitly, or order by GROUPING_ID().
-- Reliable cross-database ordering: detail first, grand total last
ORDER BY
GROUPING_ID(region, product_line) ASC, -- 0 = detail, higher = more rolled up
region NULLS LAST,
product_line NULLS LAST;
A 4-column CUBE produces 16 grouping combinations. Many of those may be meaningless for your report. If you only need 5 of the 16 combinations, use GROUPING SETS with those 5 explicitly listed. You'll get a smaller result set and likely better performance.
GROUPING SETS requires each set to be wrapped in parentheses, even if it's a single column. A common syntax error:
-- WRONG: Missing inner parentheses
GROUP BY GROUPING SETS (region, product_line, ())
-- RIGHT: Each set needs its own parens
GROUP BY GROUPING SETS ((region), (product_line), ())
The first version would be interpreted as GROUPING SETS over the columns region, product_line, and an empty set — which is actually the correct behavior in this case, but only coincidentally. For multi-column sets, the distinction is critical:
-- These are very different things:
GROUP BY GROUPING SETS (region, product_line) -- two single-column sets
GROUP BY GROUPING SETS ((region, product_line)) -- one two-column set
Remember that ROLLUP(a, b, c) always collapses from right to left. If you want a specific non-hierarchical combination, you cannot get it with ROLLUP alone. For example, ROLLUP(region, product_line) will give you (region, product_line), (region), and () — but NOT (product_line) alone. If you need (product_line) as a separate grouping, add it explicitly with GROUPING SETS or switch entirely to GROUPING SETS.
Before you copy this into production, a quick sanity check on dialect differences:
| Feature | PostgreSQL | SQL Server | MySQL 8+ | BigQuery | Snowflake |
|---|---|---|---|---|---|
| ROLLUP | ✅ | ✅ | ✅ | ✅ | ✅ |
| CUBE | ✅ | ✅ | ✅ | ✅ | ✅ |
| GROUPING SETS | ✅ | ✅ | ✅ | ✅ | ✅ |
| GROUPING() | ✅ | ✅ | ✅ | ✅ | ✅ |
| GROUPING_ID() | ✅ | ✅ | ✅ | ✅ | ✅ |
| NULLS LAST in ORDER BY | ✅ | ❌ (use ISNULL trick) | ✅ | ✅ | ✅ |
In SQL Server, to sort NULLs last:
ORDER BY ISNULL(region, 'ZZZZ'), ISNULL(product_line, 'ZZZZ')
You now have a complete picture of how multi-level aggregation works in SQL:
ROLLUP follows a hierarchy, collapsing dimensions from right to left, producing n+1 grouping levels. Use it for geographic, temporal, or organizational hierarchies.CUBE produces every possible subset of your dimensions. Use it for cross-dimensional analysis where any dimension might be the primary filter. Beware of combinatorial explosion with many columns.GROUPING SETS is the foundation both operators build on. Use it when you need precise control over exactly which aggregation levels appear — which is most of the time in production reporting.GROUPING() and GROUPING_ID() are essential companions. Always use them to distinguish rollup NULLs from data NULLs, to produce readable row labels, and to control sort order.The best way to solidify this is to grab a real dataset you work with — customer transactions, web events, operational data — and build a multi-level summary report that you'd actually use. Start with ROLLUP on a two-level hierarchy, confirm the subtotals match what you'd get from separate queries, then add GROUPING() labels.
Natural next topics to explore:
SUM() OVER () to compute percent-of-total inside a ROLLUP query without a separate CTECUBE results to serve dashboard queries at millisecond speedFILTER clauses or conditional aggregation to turn row-level groupings into column-level comparisonsThe operators you learned today are workhorses of analytics SQL. Once you're comfortable with them, you'll find yourself reaching for GROUPING SETS any time you see a UNION ALL aggregation chain — and cleaning up a lot of fragile SQL in the process.
Learning Path: Advanced SQL Queries