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, If 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.

It will be a lengthy way and increase the complexity of T-SQL code also, so to avoid this lengthy way , we can use 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.

Here we have one sample table – ProdCatLink , it has a five columns CatId, Vendor, Category , product and quantity.

 

 

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

SELECT Vendor, SUM (Quantity) AS TotalQty FROM ProdCatLink

GROUP BY Vendor

 

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

SELECT Category, SUM (Quantity) AS TotalQty FROM ProdCatLink

GROUP BY Category

 

 

To display total quantity by Vendor and category then , you can use the following query.

SELECT Vendor, Category, SUM (Quantity) AS TotalQty FROM ProdCatLink

GROUP BY Vendor, Category


 

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

SELECT  SUM (Quantity) AS TotalQty FROM ProdCatLink

 





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

First query using grouping on vendor.

Second query using grouping on category.

Third query using grouping on  vendor & category.

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 Vendor, null AS category , SUM (Quantity) AS TotalQty FROM ProdCatLink
GROUP BY Vendor

UNION ALL

SELECT null AS vendor, Category, SUM (Quantity) AS TotalQty FROM ProdCatLink
GROUP BY Category

UNION ALL

SELECT Vendor, Category, SUM (Quantity) AS TotalQty FROM ProdCatLink
GROUP BY Vendor, Category

UNION ALL

SELECT null AS vendor , null AS category , SUM (Quantity) AS TotalQty 
FROM ProdCatLink

 

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

But, it has some draw backs like query becomes quite lengthy and performance issues can be expected as you are combining the result sets of four queries into a single one.

So, to resolve this issue you can use GROUPING SETS to define each grouping set within a single query.

SELECT Vendor, Category, SUM (Quantity) AS TotalQty

FROM ProdCatLink

GROUP BY
GROUPING SETS(
(Vendor),
(Category),
(Vendor,Category),
( )
) ORDER BY vendor, category


Now you can see the above query, It gives same result set and query is quite easy to read 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(Vendor) AS Is_VendorGrouped, GROUPING(Category) AS Is_CategoryGrouped,

Vendor, Category, SUM (Quantity) AS TotalQty

FROM ProdCatLink

GROUP BY
GROUPING SETS(
(Vendor),
(Category),
(Vendor,Category),
( )
) ORDER BY vendor, category


Recommend Posts…

SQL Server CUBE




Leave a Reply

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