📊 SQL `GROUPING SETS` – Advanced Grouping for Custom Summaries

The SQL GROUPING SETS clause gives you full control over how to group your data and create multiple groupings in a single query. It’s a powerful alternative to running multiple GROUP BY queries.

With GROUPING SETS, you can avoid writing UNION ALL statements and streamline your reports and dashboards with flexible aggregations.

📘 What is SQL GROUPING SETS?

GROUPING SETS is an extension of the GROUP BY clause that allows you to define multiple groupings in a single SQL query.

🔍 Why use it?

It replaces multiple GROUP BY queries combined with UNION ALL
Makes summary reports faster and cleaner
Works great for roll-up reports, pivot tables, and multi-level aggregations

🧾 SQL GROUPING SETS Syntax

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY GROUPING SETS (
  (column1, column2),
  (column1),
  ()
);

(column1, column2) – Group by both columns
(column1) – Group by column1 only
() – Grand total (no grouping)

✅ Example: Sales Summary by Region and Year

Suppose you have a sales table:

region	year	amount
East	2023	500
East	2024	700
West	2023	600

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

🧾 Result:

region	year	total_sales
East	2023	500
East	2024	700
West	2023	600
East	NULL	1200
West	NULL	600
NULL	2023	1100
NULL	2024	700
NULL	NULL	1800

🎯 Use Cases for GROUPING SETS

📊 Create subtotal and grand total reports
📆 Summarize by date, region, or product combinations
📦 Build dashboards with flexible drilldowns
📈 Generate multiple levels of aggregation in a single query

💡 GROUPING SETS vs ROLLUP vs CUBE

Feature	GROUPING SETS	ROLLUP	CUBE
Custom grouping	✅ Fully customizable	❌ Fixed hierarchical grouping	❌ All combinations
Subtotals	✅ Yes	✅ Yes	✅ Yes
Grand totals	✅ Yes	✅ Yes	✅ Yes
Performance	✅ Better control	⚠️ Might be overkill	⚠️ Can be slow on big data

🧠 Pro Tip: Use `GROUPING()` Function

The GROUPING() function helps you detect whether a NULL value in the result is from actual data or a subtotal.

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

📝 Summary

GROUPING SETS allows you to define custom groupings in one query
It’s more flexible than ROLLUP or CUBE
Reduces the need for UNION ALL across multiple queries
Ideal for reporting and data aggregation tasks

GROUPING SETS

📊 SQL GROUPING SETS – Advanced Grouping for Custom Summaries