GROUPING SETS Concept

One Query, Multiple Grouping Levels

GROUPING SETS lets a single query compute aggregates at several different grouping levels at once — e.g., totals by region, totals by product, AND a grand total — instead of writing three separate GROUP BY queries and combining them with UNION ALL.

Running Example

All examples in this chapter use one small orders table:

sql
CREATE TABLE orders (
    id      INT PRIMARY KEY,
    region  VARCHAR(10),
    product VARCHAR(10),
    amount  NUMERIC(8,2)
);

Total of all 8 rows: 1060. Per region: East = 310, West = 550, North = 200. Per product: Widget = 530, Gadget = 530 (100+200+50+120+60 and 150+300+80).

The "Old Way": Three Queries + UNION ALL

sql
SELECT region, NULL AS product, SUM(amount) AS total_amount FROM orders GROUP BY region
UNION ALL
SELECT NULL, product, SUM(amount) FROM orders GROUP BY product
UNION ALL
SELECT NULL, NULL, SUM(amount) FROM orders;

This works, but it scans the orders table three separate times (once per SELECT) and is verbose and error-prone to keep in sync.

The GROUPING SETS Way

sql
SELECT region, product, SUM(amount) AS total_amount
FROM orders
GROUP BY GROUPING SETS ((region), (product), ())
ORDER BY region, product;

This computes all three grouping levels in one query, conceptually scanning the base table once:

• (region) → subtotal per region (3 rows), product shows NULL because that grouping set didn't group by product.
• (product) → subtotal per product (2 rows: Widget = 530, Gadget = 530 — coincidentally equal here), region shows NULL.
• () → the empty grouping set = grand total across everything (1 row, both columns NULL), amount = 1060.

Six rows total, one query, one logical scan of the base data.

Distinguishing a Subtotal Row from a Real NULL

A NULL appearing in region here means "this row is a subtotal that ignored region," not "there's an order with an unknown region." If the underlying data can also contain genuine NULLs in a grouping column, this becomes ambiguous — that's what the GROUPING() function is for: GROUPING(region) = 1 marks a subtotal row, GROUPING(region) = 0 marks a real per-region row (even one whose region happens to be an actual NULL value).

sql
SELECT region, product, SUM(amount) AS total_amount,
       GROUPING(region) AS is_region_subtotal
FROM orders
GROUP BY GROUPING SETS ((region), (product), ());

Dialect Support (Important Caveat)

GROUPING SETS is standard SQL and well supported in PostgreSQL, SQL Server, and Oracle. MySQL does not support `GROUPING SETS` (as of mainstream MySQL 8.x) — it only offers the simpler WITH ROLLUP modifier (Topic 15.7), which cannot express arbitrary, non-hierarchical combinations like "by region, by product, AND grand total" in one shot. On MySQL, the manual UNION ALL approach above is the fallback.

Edge Cases

• Grouping sets can be arbitrary and non-hierarchical: GROUPING SETS ((region, product), (region), ()) is valid and different from ROLLUP, which always nests strictly left-to-right.
• An empty set () always means "grand total across all rows," regardless of how many columns appear elsewhere in the GROUPING SETS list.
• Combining GROUPING SETS with HAVING filters the final combined result set, applied per output row (i.e., per grouping-level row), not per underlying base row.

Key Takeaways / Interview Q&A

• Q: What problem does GROUPING SETS solve?

A: Computing multiple different grouping levels (e.g., by region, by product, grand total) in one query and one logical table scan, instead of several UNIONed queries.

• Q: What does an empty grouping set `()` represent?

A: The grand total row, with every grouping column shown as NULL.

• Q: How do you tell a "real" NULL apart from a subtotal NULL?

A: Use the GROUPING() function, which returns 1 for a subtotal/rollup row and 0 for an actual per-value row.

• Q: Does MySQL support GROUPING SETS?

A: No — MySQL only supports the simpler WITH ROLLUP; full GROUPING SETS require PostgreSQL, SQL Server, or Oracle.