Home » SQL Server GROUPING SETS

SQL Server GROUPING SETS

SQL GROUPING SETS allows computing multiple GROUP BY clauses in a single statement. The results of GROUPING SETS are the equivalent of UNION ALL of the specified groups.




Suppose, you want to show a result set that groups the data by multiple combinations of group by expressions, In this case you have to create one grouped query per combination of expressions and merge the results using the UNION ALL operator.

Using such approach you may get desired result but It will be a lengthy way also increase the complexity of T-SQL code.

So to avoid this lengthy way using GROUPING SETS operator along with the GROUP BY clause and define each grouping set within a single query.

Lets look at an example of GROUPING SETS in SQL Server.

To demonstrate this first we create a sample table named ItemQty which contains the details of purchased Item and insert some records in table.

CREATE TABLE dbo.ItemQty
(
RowId INT IDENTITY(1,1),
Region VARCHAR(50),
Item VARCHAR(50),
Year INT,
Quantity INT
)
INSERT INTO dbo.ItemQty(Region, Item, Year, Quantity)
VALUES('North', 'Computer', 2019, 1300),
('South', 'Computer', 2019, 4500),
('East', 'Computer', 2019, 2500),
('North', 'Hard Disk', 2019, 900),
('West', 'Computer', 2019, 2500),
('South', 'Hard Disk', 2019, 3500),
('West', 'Hard Disk', 2019, 4500),
('North', 'Pen Drive', 2020, 1500),
('West', 'Pen Drive', 2020, 2600),
('South', 'Pen Drive', 2020, 2700),
('East', 'Pen Drive', 2020, 2100),
('East', 'Pen Drive', 2019, 1800),
('West', 'Pen Drive', 2019, 1000),
('North', 'Pen Drive', 2019, 900)

As you can see now we have a table named ItemQty as shown below.

SELECT * FROM dbo.ItemQty

Suppose, you want to display the total purchased quantity by region, then you can use the following query .

SELECT Region, SUM (Quantity) AS TotalQty 
FROM dbo.ItemQty 
GROUP BY Region

As you can see, It returns total quantity by Region.

Now, to display Total Quantity by Item, then you can use the following Query.

SELECT Item, SUM(Quantity) AS TotalQty 
FROM dbo.ItemQty 
GROUP BY Item

To display total quantity by Region and Item then you can use the following query.

SELECT Region, Item, SUM(Quantity) AS TotalQty 
FROM dbo.ItemQty 
GROUP BY Region, Item

To display the Over all total Quantity, you can use the following empty group set query.

SELECT SUM(Quantity) AS TotalQty 
FROM dbo.ItemQty




As you can see, above four queries which return four result sets using different grouping sets as defined below:

First query using grouping on Region.

Second query using grouping on Item.

Third query using grouping on  Region & Item.

Fourth query using empty grouping.

If you combine all four queries result set into single statement then you will have to use UNION ALL .

As you know UNION ALL operator requires all result set to have the same number of columns, so you need to add NULL to the select list to the queries as given below .

SELECT Region, null AS Item, SUM(Quantity) AS TotalQty  
FROM dbo.ItemQty 
GROUP BY Region
UNION ALL
SELECT null AS Region, Item, SUM(Quantity) AS TotalQty
FROM dbo.ItemQty
GROUP BY Item
UNION ALL
SELECT Region, Item, SUM(Quantity) AS TotalQty  
FROM dbo.ItemQty 
GROUP BY Region, Item
UNION ALL
SELECT null AS Region, null AS Item, SUM(Quantity) AS TotalQty 
FROM dbo.ItemQty

 

As can you see above result set, UNION ALL generate a single result with the aggregates for all grouping sets as you expected.

But such approach can cause slowness of query and performance issues can be expected as you are combining the result sets of four queries into a single one.

So, you can achieve the same result using GROUPING SETS to define each grouping set within a single query.

SELECT Region, Item, SUM(Quantity) AS TotalQty  
FROM dbo.ItemQty 
GROUP BY GROUPING SETS
(
(Region), 
(Item), 
(Region, Item), 
( )
) 
ORDER BY Region, Item 


Now you can see the above query, It gives same result set and query is quite simple, and more efficient in comparison of using UNION ALL .

In above query you can also use the GROUPING FUNCTION to see whether the specified column in GROUP BY clause is aggregated or not .

It returns two values 0 & 1. 1 means result set is aggregated or 0 means not aggregated.

Lets use Grouping function in the above query .

SELECT GROUPING(Region) AS Is_RegionGrouped, GROUPING(Item) AS Is_ItemGrouped, 
Region, Item, SUM(Quantity) AS TotalQty  
FROM dbo.ItemQty 
GROUP BY GROUPING SETS ((Region), (Item), (Region, Item), ( )) 
ORDER BY Region, Item 

Also Read..

SQL Server CUBE




 1,078 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.