GROUPING SETS, ROLLUP, and CUBE

GROUPING SETS is an extension of the GROUP BY clause that allows you 
to define multiple groupings in a single query. It's useful when you 
want subtotals and totals at different grouping levels-without running 
multiple GROUP BY queries.

SELECT region, product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY GROUPING SETS (
    (region, product),   -- detailed level
    (region),            -- subtotal per region
    ()                   -- grand total
);

Result:
region	product	total_sales
East	Apples	1000
East	Oranges	1200
East	NULL	2200
West	Apples	800
West	NULL	800
NULL	NULL	3000


ROLLUP is a shortcut extension of GROUP BY that automatically 
creates subtotals and a grand total for hierarchical groupings. 
It's commonly used for reports that need running totals across 
multiple levels of aggregation.


SELECT region, product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY ROLLUP (region, product);

Result:
region	product	total_sales
East	Apples	1000
East	Oranges	1200
East	NULL	2200
West	Apples	800
West	NULL	800
NULL	NULL	3000

CUBE is an extension of GROUP BY that generates all possible 
combinations of the specified grouping columns. It's used for 
multidimensional analysis-like pivot tables-where you want totals 
across every combination of dimensions.

SELECT region, product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY CUBE (region, product);

Result:
region	product	total_sales
East	Apples	1000
East	Oranges	1200
West	Apples	800
West	Oranges	1000
East	NULL	2200
West	NULL	1800
NULL	Apples	1800
NULL	Oranges	2200
NULL	NULL	4000