GROUPING SETS is an advanced GROUP BY feature that lets you calculate multiple groupings in a single query.

Instead of writing multiple GROUP BY queries with UNION ALL, you can use one query.

Why use GROUPING SETS?

  • Cleaner SQL
  • Better performance
  • Easier reporting queries

Step 1: Basic Syntax

SELECT column_list, aggregate_function(column)
FROM table_name
GROUP BY GROUPING SETS (
(grouping_columns),
(another_grouping),
()
);
  • Each set inside () is a separate GROUP BY
  • () = grand total

Step 2: Example table

Sales Table

SaleID Region Product Amount
1 East Laptop 1000
2 East Mobile 800
3 West Laptop 1200
4 West Mobile 700
5 North Laptop 900

Step 3: Normal GROUP BY (single grouping)

Total sales by Region

SELECT Region, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Region;

Step 4: Multiple GROUP BY using UNION ALL (old way)

SELECT Region, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY RegionUNION ALL

SELECT Product, SUM(Amount)
FROM Sales
GROUP BY Product;

❌ Repetitive and harder to maintain

Step 5: Same result using GROUPING SETS (better way)

SELECT Region, Product, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS (
(Region),
(Product)
);

Result:

Region Product TotalSales
East NULL 1800
West NULL 1900
North NULL 900
NULL Laptop 3100
NULL Mobile 1500

NULL means that column is not part of that grouping.

Step 6: GROUPING SETS with Grand Total

Add () to get overall total:

SELECT Region, Product, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS (
(Region),
(Product),
()
);

Result includes:

Region Product TotalSales
NULL NULL 4600

Step 7: GROUPING SETS with multiple columns

Group by Region & Product, plus Region only, plus Grand Total:

SELECT Region, Product, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS (
(Region, Product),
(Region),
()
);

Step 8: Using GROUPING() function

To identify subtotal vs total rows:

SELECT
Region,
Product,
SUM(Amount) AS TotalSales,
GROUPING(Region) AS IsRegionGrouped,
GROUPING(Product) AS IsProductGrouped
FROM Sales
GROUP BY GROUPING SETS (
(Region, Product),
(Region),
()
);

Categorized in:

SQL Server,