Advanced

ROLLUP, CUBE and GROUPING SETS

Build subtotal and grand-total rows for business reports using PostgreSQL grouping extensions, then understand their dialect equivalents.

Est. 14–20 min Updated Suggest a fix

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

💡 Note
Prerequisites: 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.

PostgreSQL — city and month detail, city subtotal, grand total
SELECT
  s.city,
  date_trunc('month', o.order_date)::date AS month,
  SUM(o.total_amount) AS revenue,
  GROUPING(s.city) AS city_is_rolled_up,
  GROUPING(date_trunc('month', o.order_date)) AS month_is_rolled_up
FROM orders AS o
JOIN stores AS s USING (store_id)
WHERE o.order_status = 'Delivered'
GROUP BY ROLLUP (
  s.city,
  date_trunc('month', o.order_date)::date
)
ORDER BY city_is_rolled_up, s.city, month_is_rolled_up, month;

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.

PostgreSQL — category and store-city cube
SELECT
  p.category,
  s.city,
  SUM(oi.line_total) AS sales,
  GROUPING(p.category) AS category_is_rolled_up,
  GROUPING(s.city) AS city_is_rolled_up
FROM order_items AS oi
JOIN orders AS o USING (order_id)
JOIN products AS p USING (product_id)
JOIN stores AS s USING (store_id)
WHERE o.order_status = 'Delivered'
GROUP BY CUBE (p.category, s.city)
ORDER BY category_is_rolled_up, p.category,
         city_is_rolled_up, s.city;

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.

⚠️ Important
A cube does not “group by all columns” in the ordinary sense. It emits several kinds of subtotal row. Each extra dimension doubles the set of grouping combinations, so a broad cube can be expensive and difficult to interpret.

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

PostgreSQL — details, city totals, month totals, and grand total
SELECT
  s.city,
  date_trunc('month', o.order_date)::date AS month,
  SUM(o.total_amount) AS revenue,
  GROUPING(s.city) AS city_rolled_up,
  GROUPING(date_trunc('month', o.order_date)::date) AS month_rolled_up
FROM orders AS o
JOIN stores AS s USING (store_id)
WHERE o.order_status = 'Delivered'
GROUP BY GROUPING SETS (
  (s.city, date_trunc('month', o.order_date)::date),
  (s.city),
  (date_trunc('month', o.order_date)::date),
  ()
);

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.

Label subtotal rows without confusing them with missing source values
SELECT
  CASE
    WHEN GROUPING(s.city) = 1 THEN 'All cities'
    WHEN s.city IS NULL THEN '(city not recorded)'
    ELSE s.city
  END AS city_label,
  CASE
    WHEN GROUPING(date_trunc('month', o.order_date)::date) = 1
      THEN 'All months'
    ELSE to_char(date_trunc('month', o.order_date), 'YYYY-MM')
  END AS month_label,
  SUM(o.total_amount) AS revenue
FROM orders AS o
JOIN stores AS s USING (store_id)
WHERE o.order_status = 'Delivered'
GROUP BY GROUPING SETS (
  (s.city, date_trunc('month', o.order_date)::date),
  (s.city),
  (date_trunc('month', o.order_date)::date),
  ()
);

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

EngineSupport patternPortability note
PostgreSQLROLLUP, CUBE, GROUPING SETS, and GROUPING()Use standard grouping syntax; expression casts and date functions are PostgreSQL-specific.
MySQLROLLUP 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 ServerROLLUP, CUBE, GROUPING SETS, GROUPING(), and GROUPING_ID()Use SQL Server date expressions and aliases; confirm the target compatibility level.
SnowflakeROLLUP, 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.

🎯 Pro Tip
Document the grain of each output row—detail, subtotal, or grand total—so application code and dashboard users do not accidentally add a subtotal to its underlying detail rows.

// 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:

  1. Write a ROLLUP for delivered order revenue by city and month. Identify every output grain.
  2. Write GROUPING SETS for city-month detail, city total, month total, and grand total.
  3. For category and city, predict the four grouping levels emitted by CUBE before running it.
  4. Add a genuine unknown-city label and a separate “all cities” label using GROUPING().
  5. Rewrite the selected levels using UNION ALL for 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.

Lesson 60 → Working with JSON in SQL

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.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...