SQL GROUPING SETS, ROLLUP, and CUBE – Advanced Aggregations

🔹 Why These Operators Matter

GROUPING SETS, ROLLUP, and CUBE extend GROUP BY to create multiple summary levels within a single query. They are indispensable for dashboards, OLAP-style analysis, and multi-level reporting.

⚙️ GROUPING SETS

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

Generates custom combinations: totals by region, by product, and the overall grand total in one pass.

⚙️ ROLLUP

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

Builds hierarchical subtotals from detailed rows → region totals → overall totals.

⚙️ CUBE

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

Returns every combination of the specified dimensions—perfect for multi-dimensional analytics.

🧠 GROUPING() Helper

SELECT
  region,
  product,
  SUM(amount) AS total_sales,
  GROUPING(region) AS region_flag,
  GROUPING(product) AS product_flag
FROM sales
GROUP BY CUBE (region, product);

⚖️ Comparison Snapshot

💡 Practical Use Cases

• Sales reporting (region ↔ product ↔ total)
• Financial statements (department subtotals)
• Executive dashboards with drill-downs
• OLAP cubes for BI tools like Power BI or Tableau

🧠 Interview Tip