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..
3,018 total views, 1 views today