Advanced Grouping (ROLLUP, CUBE, SETS) 📊¶

Prerequisites: GROUP BY Clause

Mentor's Note: A standard GROUP BY gives you one level of summary. But high-level managers want to see Subtotals and Grand Totals in one report. Oracle's extensions make this easy without writing multiple queries! 💡

🌟 The Scenario: The Sales Dashboard 📈¶

Imagine a store selling Electronics in different cities. - Goal: You want to see: 1. Total sales per Product per City. 2. Total sales per Product (all cities). 3. Total sales for the whole store (Grand Total). ✅

💻 1. ROLLUP (Hierarchical Subtotals)¶

Generates subtotals from right to left.

-- Scenario: City -> Store -> Sales
SELECT city, store_id, SUM(sales)
FROM shop_sales
GROUP BY ROLLUP(city, store_id);
-- Result: Detail rows, Subtotals for each City, and a Grand Total.

💻 2. CUBE (Cross-Tab Subtotals)¶

Generates subtotals for every possible combination of columns.

SELECT city, product_name, SUM(sales)
FROM shop_sales
GROUP BY CUBE(city, product_name);
-- Result: Subtotals for every City, Subtotals for every Product, and Grand Total.

💻 3. GROUPING SETS (Custom Subtotals)¶

The most precise tool. You specify exactly which groups you want to see.

SELECT city, product_name, SUM(sales)
FROM shop_sales
GROUP BY GROUPING SETS ((city), (product_name));
-- Result: Shows only City totals and Product totals (no detail rows!).

🏗️ Architect's Note: Performance Warning 🛡️¶

These operations generate many extra rows. - The Architect's Secret: Internally, Oracle performs multiple passes or a single optimized pass over the data. - Tip: Avoid using CUBE on more than 3 or 4 columns at once. A CUBE of 10 columns can generate 2^10 (1,024) combinations for every row, which can hang your database!