Advanced SQL

GROUPING SETS, ROLLUP, and CUBE

Advanced grouping produces detail and multiple subtotal levels in one result: GROUPING SETS lists exact levels, ROLLUP builds hierarchical prefixes, and CUBE builds every dimension combination.

GROUPING SETS states the exact grouping keys required.
ROLLUP(a,b) produces hierarchical prefixes.
CUBE(a,b) produces every combination.
GROUPING(column) distinguishes subtotal placeholders from data nulls.
Filters apply before subtotal construction; HAVING applies to generated groups.

Customer/status detail, customer subtotals, and grand total

SELECT CASE WHEN GROUPING(o.customer_id) = 1 THEN 'ALL CUSTOMERS'
            ELSE CAST(o.customer_id AS varchar(20)) END AS customer_label,
       CASE WHEN GROUPING(o.status) = 1 THEN 'ALL STATUSES'
            ELSE o.status END AS status_label,
       SUM(i.quantity * i.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
GROUP BY ROLLUP (o.customer_id, o.status)
ORDER BY GROUPING(o.customer_id), o.customer_id,
         GROUPING(o.status), o.status;

Grouping expansion
Construct	Generated grouping sets for `(customer, status)`	Rows
Explicit	`(customer,status), (customer), ()`	Chosen levels
`ROLLUP`	`(customer,status), (customer), ()`	Hierarchy prefixes
`CUBE`	`(customer,status), (customer), (status), ()`	All combinations

Both grouping dimensions are single-valued attributes of each order, while revenue comes from its order-line quantity times transaction price. The join therefore contributes each line exactly once at the detail, customer-subtotal, and grand-total levels. GROUPING(o.status)=1 identifies a generated subtotal placeholder rather than a stored status.