🔹 Advanced SQL: CUBE, ROLLUP & GROUPING SETS

📊 SQL CUBE, ROLLUP & GROUPING SETS with Examples

When you want to analyze data from multiple perspectives, SQL provides advanced grouping features like CUBE, ROLLUP, and GROUPING SETS. These are useful for generating subtotals, grand totals, and cross-tab reports.

📋 Sample sales Table:

region	product	sales_amount
Dhaka	TV	30000
Dhaka	Fridge	25000
Chittagong	TV	28000
Chittagong	Fridge	20000

🔹 1. Using ROLLUP

ROLLUP creates subtotals and a grand total by progressively removing elements from right to left in the GROUP BY list.

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

Output:

region	product	total_sales
Chittagong	Fridge	20000
Chittagong	TV	28000
Chittagong	(null)	48000
Dhaka	Fridge	25000
Dhaka	TV	30000
Dhaka	(null)	55000
(null)	(null)	103000

🔹 2. Using CUBE

CUBE computes all combinations of grouping columns including subtotals and cross-totals.

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

Output:

region	product	total_sales
Chittagong	Fridge	20000
Chittagong	TV	28000
Chittagong	(null)	48000
Dhaka	Fridge	25000
Dhaka	TV	30000
Dhaka	(null)	55000
(null)	Fridge	45000
(null)	TV	58000
(null)	(null)	103000

🔹 3. Using GROUPING SETS

GROUPING SETS lets you specify exactly which group combinations you want (like custom rollups).

SELECT region, product, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS (
  (region),
  (product),
  (region, product)
);

Output:

region	product	total_sales
Chittagong	Fridge	20000
Chittagong	TV	28000
Dhaka	Fridge	25000
Dhaka	TV	30000
Chittagong	(null)	48000
Dhaka	(null)	55000
(null)	Fridge	45000
(null)	TV	58000

🧠 Summary:

• ROLLUP: Adds subtotals and grand total from left to right
• CUBE: Generates all possible subtotal combinations
• GROUPING SETS: Gives custom control over groupings

SQL ROLLUP example, SQL CUBE Bangla, GROUPING SETS Oracle, advanced SQL GROUP BY, Bangladesh sales data, SQL subtotal query, SQL total report, MySQL rollup, cube analysis SQL, SQL for data analysis