Multiple Grouping Sets in SQL Server

We know the GROUP BY clause groups table data. With it we can also do multiple grouping sets. We need a single query to group the data in multiple combinations.

In SQL Server three clauses allow multiple grouping sets, grouping sets, Cube and Rollup.

Each of them is shown in this article with examples.

  1. USE DEMOS ;
Create the EMP Table
  1. CREATE TABLE DBO.EMP   
  2. (  
  3. ID INT IDENTITY(1, 1) PRIMARY KEY,  
  4. FIRTSNAME VARCHAR(100),  
  5. LASTNAME VARCHAR(100),  
  6. LOCATION VARCHAR(100),  
  7. DOB DATETIME,  
  8. SALARY MONEY,  
  9. DEPT INT  
  10. )  

Merge Statement

Insert data using Merge statement as in the following:

  1. MERGE INTO DBO.EMP AS T_EMP USING (  
  2. VALUES  
  3. (  
  4. 'RAKESH''KALLURI''HYDERABAD',  
  5. '07-23-1989', 24000, 1  
  6. ),  
  7. (  
  8. 'NARESH''CH''PUNE''07-23-1987',  
  9. 48000, 1  
  10. ),  
  11. (  
  12. 'SRUJAN''KUMAR''HYDERABAD''07-23-1988',  
  13. 25000, 1  
  14. ),  
  15. (  
  16. 'VENKATESH''BODUPPALY''HYDERABAD',  
  17. '07-23-1986', 32000, 2  
  18. ),  
  19. (  
  20. 'ALI''MD''HYDERABAD''07-23-1987',  
  21. 38000, 2  
  22. ),  
  23. (  
  24. 'GANGA''RAJAYAM''PUNE''05-26-1987',  
  25. 390000, 2  
  26. ),  
  27. (  
  28. 'RAVI''KUMAR''CHENNAI''03-23-1986',  
  29. 47000, 1  
  30. ),  
  31. (  
  32. 'PRAVEEN''KUMAR''DELHI''07-23-1988',  
  33. 33000, 2  
  34. )  
  35. AS S_EMP(  
  36. FIRTSNAME, LASTNAME, LOCATION, DOB,  
  37. SALARY, DEPT  
  38. ON 1 = 2 WHEN NOT MATCHED THEN INSERT(  
  39. FIRTSNAME, LASTNAME, LOCATION, DOB,  
  40. SALARY, DEPT  
  41. )  
  42. VALUES  
  43. (  
  44. S_EMP.FIRTSNAME, S_EMP.LASTNAME,  
  45. S_EMP.LOCATION, S_EMP.DOB, S_EMP.SALARY,  
  46. S_EMP.DEPT  
  47. );  
Check the Data
  1. SELECT * FROM DBO.EMP;  

  1. SELECT DEPT ,COUNT(*) [COUNTFROM DBO.EMP GROUP BY DEPT;  
  2. SELECT YEAR(DOB) ,COUNT(*) [COUNTFROM DBO.EMP GROUP BY YEAR(DOB);  


Grouping Sets

In grouping sets we can provide multiple grouping sets with the () parenthesis symbol. The new grouping is separated by a "," (comma). If we want empty grouping set means all rows combination ().

(): Means an empty grouping set.

Example

  1. SELECT DEPT,YEAR(DOB) [YEAR],COUNT(*) [COUNTFROM DBO.EMP   
  2. GROUP BY   
  3. GROUPING   
  4. SETS ((DEPT, YEAR(DOB)), (DEPT), (YEAR(DOB)), () );


In the preceding example 4, the grouping sets combinations of data is grouped together. By using Union or Union all, we can get the same result.
  1. SELECT  
  2. DEPT,  
  3. YEAR(DOB) [COUNT],  
  4. COUNT(*) [COUNT]  
  5. FROM  
  6. DBO.EMP  
  7. GROUP BY  
  8. DEPT,  
  9. YEAR(DOB)  
  10. UNION ALL  
  11. SELECT  
  12. DEPT,  
  13. NULL [COUNT],  
  14. COUNT(*) [COUNT]  
  15. FROM  
  16. DBO.EMP  
  17. GROUP BY  
  18. DEPT  
  19. UNION ALL  
  20. SELECT  
  21. NULL DEPT,  
  22. YEAR(DOB) [COUNT],  
  23. COUNT(*) [COUNT]  
  24. FROM  
  25. DBO.EMP  
  26. GROUP BY  
  27. YEAR(DOB)  
  28. UNION ALL  
  29. SELECT  
  30. NULL DEPT,  
  31. NULL [COUNT],  
  32. COUNT(*) [COUNT]  
  33. FROM  
  34. DBO.EMP  
The four possible combinations are:



Cube

The Cube clause also defines the list of grouping sets, it also generates the empty grouping set.

Example
  1. SELECT DEPT,YEAR(DOB) [YEAR],COUNT(*) [COUNTFROM DBO.EMP   
  2. GROUP BY CUBE((DEPT),(YEAR(DOB)))  


Rollup

The Rollup clause gives the hierarchy form result, it also generates the empty grouping set.

Example
  1. SELECT  
  2. DEPT,  
  3. YEAR(DOB) [YEAR],  
  4. COUNT(*) [COUNT]  
  5. FROM  
  6. DBO.EMP  
  7. GROUP BY  
  8. ROLLUP(  
  9. (DEPT),  
  10. (  
  11. YEAR(DOB)  
  12. )  
  13. )  
Here hierarchy means in the preceding example 3 possible combinations exist
  1. (Dept, Year(DOB))
  2. (Dept)
  3. ()


Grouping

The Grouping function identifies the grouping set that accepts one input column, if that column is in that grouping set then the result is 0. Grouping can be used in only Select, Having and Order by clauses.

For more information about Grouping.

If the column is not in that grouping set the results is -1.

Example

0: Means it is part of the grouping set.

1: Means it is not part of the grouping set.

  1. SELECT DEPT,GROUPING(DEPT) GRP_DEPT,YEAR(DOB) [YEAR],GROUPING(YEAR(DOB)) GRP_YEAR ,COUNT(*) [COUNTFROM DBO.EMP   
  2. GROUP BY ROLLUP ((DEPT), (YEAR(DOB)))  


Examples of Actual Scenarios

The following is for a salary by department:
  1. SELECT  
  2. DEPT,  
  3. SUM(SALARY) AS [SALARY]  
  4. FROM  
  5. EMP  
  6. GROUP BY  
  7. DEPT WITH ROLLUP --OR--  
  8. SELECT  
  9. DEPT,  
  10. SUM(SALARY) AS [SALARY]  
  11. FROM  
  12. EMP  
  13. GROUP BY  
  14. ROLLUP(DEPT)