What is the difference between ROLLUP and CUBE?

ROLLUP creates hierarchical subtotals from right to left (n+1 groupings). CUBE creates all possible combinations (2^n groupings). Use ROLLUP for hierarchies, CUBE for multi-dimensional analysis.

How does GROUPING() function work and why is it useful?

GROUPING(column) returns 1 if the row is a subtotal where that column is aggregated (NULL), 0 if it is detail data. Use it to identify and label subtotal rows, replacing NULL with meaningful text like "ALL REGIONS".

When would you use GROUPING SETS instead of ROLLUP or CUBE?

Use GROUPING SETS when you need specific combinations without all the combinations CUBE creates. More flexible and efficient when you do not need every possible grouping combination.

Create sales report with region and product subtotals plus grand total

SELECT

COALESCE(region, 'ALL REGIONS') AS region,

COALESCE(product, 'ALL PRODUCTS') AS product,

SUM(sales) AS total_sales

FROM orders

GROUP BY ROLLUP(region, product)

ORDER BY region NULLS LAST, product NULLS LAST;

ROLLUP creates hierarchical subtotals. COALESCE replaces NULL with labels. NULLS LAST ensures logical ordering.