SQL GROUPING SETS, ROLLUP, and CUBE

Source: DataDriven analysis of 1,042 verified data engineering interview rounds.

GROUPING SETS: Pick Your Groupings

GROUPING SETS lets you specify exactly which column combinations to aggregate. Each grouping set is a parenthesized list of columns. The empty set () produces the grand total. The engine computes all groupings in a single scan of the data.

-- Revenue by region, by product, and grand total
SELECT
  region,
  product,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY GROUPING SETS (
  (region),           -- subtotal per region
  (product),          -- subtotal per product
  ()                  -- grand total
);

Read that query slowly. You're asking the engine for three different groupings in one pass, and you get rows back for each one, each row labeled by NULL where a dimension was rolled up. The first time you see the output, you'll want to sort it so the NULL patterns make sense. That instinct is fine. Most teams add an ORDER BY with a GROUPING() helper once they're building real dashboards, which we'll cover in a minute.

Equivalent UNION ALL

To understand what GROUPING SETS produces, here is the long-form version. Notice how much more code it requires and how it scans the table three times instead of once.

-- The UNION ALL equivalent (3 scans, more code)
SELECT region, NULL AS product, SUM(revenue)
FROM sales GROUP BY region

UNION ALL

SELECT NULL, product, SUM(revenue)
FROM sales GROUP BY product

UNION ALL

SELECT NULL, NULL, SUM(revenue)
FROM sales;

ROLLUP: Hierarchical Subtotals

ROLLUP is a shorthand that generates a hierarchical set of groupings. It removes columns from right to left. ROLLUP(a, b, c) produces: (a, b, c), (a, b), (a), and (). This matches hierarchical dimensions perfectly. Year, quarter, month. Country, state, city. Department, team, employee.

-- Revenue with hierarchical subtotals
SELECT
  year,
  quarter,
  month,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY ROLLUP (year, quarter, month);

-- This generates these groupings:
-- (year, quarter, month)  -> monthly detail
-- (year, quarter)         -> quarterly subtotal
-- (year)                  -> yearly subtotal
-- ()                      -> grand total

ROLLUP generates N+1 groupings for N columns. Three columns gives you four levels: detail, two levels of subtotals, and the grand total. The column order matters. ROLLUP(year, quarter) gives you year subtotals. ROLLUP(quarter, year) gives you quarter subtotals. Put the broadest dimension first.

Partial ROLLUP

You can combine regular GROUP BY columns with ROLLUP to get subtotals for some dimensions but not others.

-- Subtotals per region, but always grouped by region
SELECT
  region,
  product_category,
  product,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY region, ROLLUP (product_category, product);
-- Produces per region:
--   (region, product_category, product) -> detail
--   (region, product_category)          -> category subtotal
--   (region)                            -> region subtotal

CUBE: Every Possible Combination

CUBE generates all possible grouping combinations. For N columns, it produces 2^N groupings. CUBE(a, b) gives: (a, b), (a), (b), and (). CUBE(a, b, c) gives 8 groupings. This is useful for cross-tab reports where you need aggregates along every dimension.

-- Full cross-tab: every combination of region and product
SELECT
  region,
  product,
  SUM(revenue) AS total_revenue,
  COUNT(*) AS num_transactions
FROM sales
GROUP BY CUBE (region, product);

-- This generates these groupings:
-- (region, product)  -> detail per region + product
-- (region)           -> subtotal per region
-- (product)          -> subtotal per product
-- ()                 -> grand total

Be careful with CUBE on many columns. Four columns produce 16 groupings. Six columns produce 64. The result set grows fast, and so does the computation. Use CUBE for reporting summaries with 2 to 4 dimensions. For more dimensions, use GROUPING SETS to specify only the combinations you actually need.

ROLLUP vs. CUBE Comparison

The GROUPING() Function

When a column appears as NULL in GROUPING SETS output, you need to know: is it NULL because the data is NULL, or because this row is a subtotal where that column was rolled up? The GROUPING() function answers this. GROUPING(column_name) returns 1 if the column is aggregated (subtotal row) and 0 if it is part of the current grouping (detail row).

SELECT
  CASE
    WHEN GROUPING(region) = 1 THEN 'All Regions'
    ELSE region
  END AS region_label,
  CASE
    WHEN GROUPING(product) = 1 THEN 'All Products'
    ELSE product
  END AS product_label,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE (region, product)
ORDER BY
  GROUPING(region),
  GROUPING(product),
  region,
  product;

The CASE expressions replace the ambiguous NULLs with readable labels. The ORDER BY uses GROUPING() values to sort subtotals after their detail rows, which is the standard pattern for formatted reports.

GROUPING_ID() for Bitmask Identification

Some engines support GROUPING_ID() (SQL Server) or GROUPING(col1, col2, ...) (PostgreSQL) which returns a bitmask identifying the current grouping level. Each bit corresponds to one column. This is useful for filtering to specific grouping levels in downstream processing.

-- PostgreSQL: multi-column GROUPING returns a bitmask
SELECT
  region,
  product,
  SUM(revenue) AS total_revenue,
  GROUPING(region, product) AS grp_level
FROM sales
GROUP BY CUBE (region, product);
-- grp_level 0 = detail, 1 = product rolled up,
-- 2 = region rolled up, 3 = grand total

Practical Reporting Examples

CREATE TABLE sales_summary AS
SELECT
  COALESCE(region, 'ALL') AS region,
  COALESCE(category, 'ALL') AS category,
  EXTRACT(YEAR FROM sale_date) AS year,
  SUM(amount) AS total_amount,
  COUNT(*) AS num_sales,
  AVG(amount) AS avg_amount,
  GROUPING(region, category) AS grp_level
FROM sales
GROUP BY
  GROUPING SETS (
    (region, category, EXTRACT(YEAR FROM sale_date)),
    (region, EXTRACT(YEAR FROM sale_date)),
    (category, EXTRACT(YEAR FROM sale_date)),
    (EXTRACT(YEAR FROM sale_date))
  );

SELECT
  cohort_month,
  activity_month,
  COUNT(DISTINCT user_id) AS active_users
FROM user_activity
GROUP BY ROLLUP (cohort_month, activity_month)
ORDER BY cohort_month NULLS LAST, activity_month NULLS LAST;

3 GROUPING SETS Interview Questions

These questions test whether you know multi-level aggregation beyond basic GROUP BY. They appear in interviews at companies that build reporting pipelines and summary tables.

Engine Support

MySQL is the notable gap: it supports ROLLUP with the WITH ROLLUP syntax but does not support GROUPING SETS or CUBE. If you need full GROUPING SETS on MySQL, use UNION ALL as a fallback.

GROUPING SETS FAQ

Related Guides