GROUPING SETS

Advanced

Aggregation & Grouping

GROUPING SETS lets you define multiple grouping combinations in a single query, avoiding verbose UNION ALL rewrites.

Overview

GROUPING SETS is a SQL:1999 extension supported by PostgreSQL, SQL Server, and BigQuery. Instead of running separate GROUP BY queries and combining with UNION ALL, you declare multiple grouping combinations in one pass. The engine scans the data once and produces subtotal rows for each combination. NULL values in result rows can mean either a missing group key (from GROUPING SETS) or a genuine NULL in the data — the GROUPING() function disambiguates. ROLLUP and CUBE are syntactic sugar over GROUPING SETS. Mastering GROUPING SETS is essential for building flexible OLAP-style reports.

GROUPING SETS vs UNION ALL

The canonical use case is aggregating sales by (city), by (product category), and by (city, category) in one query. The UNION ALL approach requires three separate scans; GROUPING SETS does it in one.

SQL — GROUPING SETS vs UNION ALL, orders + users + products

-- Requirement: total order amount grouped by city only,
-- by product category only, and by (city + category) together.

-- Anti-pattern: three scans with UNION ALL
SELECT city, NULL AS category, SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY u.city

UNION ALL

SELECT NULL, p.category, SUM(o.amount)
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY p.category

UNION ALL

SELECT u.city, p.category, SUM(o.amount)
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
GROUP BY u.city, p.category;

-- Better: single scan with GROUPING SETS
SELECT
    u.city,
    p.category,
    SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
GROUP BY GROUPING SETS (
    (u.city),          -- group 1: by city only
    (p.category),      -- group 2: by category only
    (u.city, p.category) -- group 3: both
);

GROUPING() Function — Distinguishing NULL from Missing Group

When GROUPING SETS omits a column from a grouping combination, that column appears as NULL in the result. If the underlying data also contains NULLs, GROUPING() tells you which NULLs are "rolled-up" placeholders vs real data NULLs.

SQL — GROUPING() function to label rollup NULLs

-- GROUPING(col) returns 1 when the column is not part of the current grouping,
-- 0 when it IS part of the grouping (even if the value happens to be NULL).

SELECT
    u.city,
    p.category,
    SUM(o.amount)          AS revenue,
    GROUPING(u.city)       AS city_is_rollup,   -- 1 = city omitted from this group
    GROUPING(p.category)   AS cat_is_rollup     -- 1 = category omitted from this group
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
GROUP BY GROUPING SETS (
    (u.city),
    (p.category),
    (u.city, p.category)
);

-- Use GROUPING() to produce readable labels instead of raw NULLs:
SELECT
    CASE WHEN GROUPING(u.city)     = 1 THEN 'ALL CITIES'     ELSE u.city     END AS city,
    CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category END AS category,
    SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
GROUP BY GROUPING SETS ((u.city), (p.category), (u.city, p.category));

ROLLUP and CUBE as GROUPING SETS Shorthand

ROLLUP(a, b) expands to GROUPING SETS ((a,b),(a),()), producing hierarchical subtotals. CUBE(a,b) expands to all four combinations. Use GROUPING SETS when you need a custom subset of those combinations.

SQL — ROLLUP/CUBE expressed as GROUPING SETS

-- ROLLUP(city, category) is equivalent to:
GROUP BY GROUPING SETS ((city, category), (city), ())

-- CUBE(city, category) is equivalent to:
GROUP BY GROUPING SETS ((city, category), (city), (category), ())

-- Custom subset (skip the grand total row that CUBE would add):
GROUP BY GROUPING SETS ((city, category), (city), (category))
-- This is what neither ROLLUP nor CUBE can express directly.

Key Points to Remember

1GROUPING SETS replaces multiple UNION ALL aggregations with a single table scan.
2GROUPING(col) returns 1 when the column is absent from the current grouping combination — use it to distinguish rollup NULLs from data NULLs.
3ROLLUP generates hierarchical subtotals; CUBE generates all combinations — both are syntactic sugar over GROUPING SETS.
4Use a custom GROUPING SETS list when you want a strict subset of what CUBE would produce.
5Supported in PostgreSQL 9.5+, SQL Server 2008+, BigQuery, and Snowflake.

Interview Questions

How does GROUPING SETS differ from ROLLUP and CUBE? Give an example where GROUPING SETS is the right choice over both.

HardGoogle

What does GROUPING() return and why is it needed?

MediumAmazon

Rewrite a three-way UNION ALL aggregation query using GROUPING SETS.

MediumMicrosoft

What are the performance benefits of GROUPING SETS over UNION ALL?

EasyFlipkart

Ask Aria about GROUPING SETS

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

GROUP BY ROLLUP & CUBE

DISTINCT vs GROUP BY