ROLLUP, CUBE and GROUPING SETS
Build subtotal and grand-total rows for business reports using PostgreSQL grouping extensions, then understand their dialect equivalents.
// Part 01
One report, several aggregation levels
A regular GROUP BY returns one row per requested group. A report may also need subtotals by city, totals by product category, and a grand total. The naive approach is several queries joined with UNION ALL. SQL grouping extensions request those aggregation levels together, usually with less repeated query logic.
We will use FreshCart orders and stores. The business question is: how much delivered sales did each city earn by month, what was each city’s monthly subtotal, and what was the monthly total across all cities? The same idea applies to product category, channel, and region dimensions.
GROUP BY, aggregates, and handling NULL. Objective: choose between the hierarchical subtotal pattern (ROLLUP), every combination (CUBE), or explicitly listed levels (GROUPING SETS).// Part 02
ROLLUP: hierarchical subtotals
ROLLUP(a, b, c) produces a hierarchy of grouping levels: (a, b, c), (a, b), (a), then () for the grand total. Think “detail, subtotal, subtotal, overall.” The order of the columns matters because the roll-up follows the listed hierarchy.
Here ROLLUP(city, month) creates detail rows by city and month, a subtotal for each city, and one grand total. It does not create a separate subtotal for each month across all cities, because that is not one of this hierarchy’s levels. Add the levels you need deliberately, or use explicit grouping sets.
// Part 03
CUBE: every combination of dimensions
CUBE(a, b) computes all four combinations: (a, b), (a), (b), and (). With three dimensions it generates eight grouping combinations. This is useful for multidimensional summaries, but the number of grouping levels grows exponentially: 2ⁿ combinations for n dimensions.
The result includes category-by-city detail, category totals across cities, city totals across categories, and the grand total. A full cube over many columns can create a large and confusing result. Use only dimensions that answer an actual reporting question.
// Part 04
GROUPING SETS: name exactly the levels you need
When the report needs a few non-hierarchical combinations, GROUPING SETS is clearer than CUBE. It accepts the exact grouping combinations. An empty set () requests the grand total. It can also combine a rollup with independent groups.
This produces four clearly defined levels: city-month detail, city subtotal, month subtotal, and grand total. Each row still has the same output columns; dimensions not included in that row’s grouping set are set to NULL.
// Part 05
Distinguish a subtotal NULL from source data NULL
A displayed NULL can mean “this actual source value is unknown” or “the grouping extension omitted this dimension for a subtotal.” Those meanings are not the same. Use the GROUPING(column) function to produce a flag, and label the output with a CASE. This avoids reporting a city that is genuinely NULL as though it were a grand total.
The GROUPING function returns 1 when that expression is absent from the current grouping set and 0 when it is present. Use separate flags for each dimension you need to label. If order matters, sort by those flags and the original dimensions rather than relying on output order.
// Part 06
Dialect notes and a practical choice
| Engine | Support pattern | Portability note |
|---|---|---|
| PostgreSQL | ROLLUP, CUBE, GROUPING SETS, and GROUPING() | Use standard grouping syntax; expression casts and date functions are PostgreSQL-specific. |
| MySQL | ROLLUP is available with GROUP BY; GROUPING() distinguishes super-aggregate rows in supported versions. | Do not assume CUBE or GROUPING SETS syntax exists; express selected levels with UNION ALL if necessary. |
| SQL Server | ROLLUP, CUBE, GROUPING SETS, GROUPING(), and GROUPING_ID() | Use SQL Server date expressions and aliases; confirm the target compatibility level. |
| Snowflake | ROLLUP, CUBE, GROUPING SETS, GROUPING(), and GROUPING_ID() | The grouping concepts are similar; use Snowflake-specific date functions and verify exact expression rules. |
Choose ROLLUP for a natural parent-to-child hierarchy, CUBE when all dimensional combinations are useful and manageable, and GROUPING SETS when the reporting specification lists exact levels. If your engine lacks the needed extension, separate aggregate queries combined with UNION ALL are explicit and portable, though more verbose.
// Part 07
Common mistakes and practice
1. Reading every NULL as a subtotal marker
Source data can have real NULLs. Use GROUPING() flags to distinguish omitted grouping columns from missing source values.
2. Assuming ROLLUP generates every subtotal
ROLLUP only creates the hierarchy implied by the listed column order. Use GROUPING SETS when you need independent month and city totals.
3. Using CUBE for too many dimensions
The combinations grow as 2ⁿ. Limit dimensions to those needed, or list the required combinations explicitly.
4. Double-counting summary rows
Detail rows and their totals coexist. Filter by grouping flags or use the right grain before aggregating this report again.
Practice tasks:
- Write a
ROLLUPfor delivered order revenue by city and month. Identify every output grain. - Write
GROUPING SETSfor city-month detail, city total, month total, and grand total. - For category and city, predict the four grouping levels emitted by
CUBEbefore running it. - Add a genuine unknown-city label and a separate “all cities” label using
GROUPING(). - Rewrite the selected levels using
UNION ALLfor an engine that lacks grouping sets.
🎯 Key Takeaways
- ✓ROLLUP emits hierarchical detail and subtotal levels in the listed order.
- ✓CUBE emits every combination of its dimensions, growing exponentially.
- ✓GROUPING SETS names exactly which aggregation levels to return.
- ✓GROUPING(column) distinguishes a subtotal placeholder NULL from a real source NULL.
- ✓Subtotal rows coexist with detail rows; define the row grain and avoid double-counting.
- ✓Dialect support varies, especially for MySQL CUBE and GROUPING SETS.
Next
Next, work with semi-structured values stored alongside relational data using JSON and PostgreSQL jsonb.
HELP US IMPROVE
How was this lesson?
Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
