Multiple Grouping Sets in SQL Server

Introduction

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.

USE DEMOS ;

Create the EMP Table

CREATE TABLE DBO.EMP    (
     ID INT IDENTITY(1,  1)  PRIMARY KEY, 
     FIRTSNAME VARCHAR(100), 
     LASTNAME VARCHAR(100), 
     LOCATION VARCHAR(100), 
     DOB DATETIME, 
     SALARY MONEY, 
     DEPT INT  
)  

Merge Statement

Insert data using the Merge statement as in the following:

MERGE INTO DBO.EMP AS T_EMP USING (
     VALUES   (
       'RAKESH',   'KALLURI',   'HYDERABAD', 
       '07-23-1989',  24000,  1  
  ), 
     (
       'NARESH',   'CH',   'PUNE',   '07-23-1987', 
       48000,  1  
  ), 
     (
       'SRUJAN',   'KUMAR',   'HYDERABAD', 
      '07-23-1988',    25000,  1  
  ), 
     (
       'VENKATESH',   'BODUPPALY',   'HYDERABAD', 
       '07-23-1986',  32000,  2  
  ), 
     (
       'ALI',   'MD',   'HYDERABAD', 
      '07-23-1987',    38000,  2  
  ), 
     (
       'GANGA',   'RAJAYAM',   'PUNE', 
      '05-26-1987',    390000,  2  
  ), 
     (
       'RAVI',   'KUMAR',   'CHENNAI', 
      '03-23-1986',    47000,  1  
  ), 
     (
       'PRAVEEN',   'KUMAR',   'DELHI', 
      '07-23-1988',    33000,  2  
  )   
)  AS S_EMP(
     FIRTSNAME,  LASTNAME,  LOCATION, 
   DOB,    SALARY,  DEPT  
)  ON 1  =  2 WHEN NOT MATCHED THEN INSERT(
     FIRTSNAME,  LASTNAME,  LOCATION, 
   DOB,    SALARY,  DEPT  
)    VALUES   (
     S_EMP.FIRTSNAME,  S_EMP.LASTNAME, 
     S_EMP.LOCATION,  S_EMP.DOB, 
   S_EMP.SALARY,    S_EMP.DEPT  
);
 

Check the Data

SELECT * FROM DBO.EMP;  

SELECT DEPT ,COUNT(*) [COUNT] FROM DBO.EMP GROUP BY DEPT;  
SELECT YEAR(DOB) ,COUNT(*) [COUNT] FROM DBO.EMP GROUP BY YEAR(DOB);  

Grouping Sets in SQL Server 

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

(): Means an empty grouping set.

Example

SELECT DEPT, 
YEAR(DOB)  [YEAR], 
COUNT(*)  [COUNT] FROM DBO.EMP    GROUP BY    GROUPING    SETS (
  (
    DEPT, 
     YEAR(DOB)
  ), 
   (DEPT), 
   (
    YEAR(DOB)
  ), 
   ()  
);

In the preceding example 4, the Grouping sets combinations of data are grouped. By using Union or Union all, we can get the same result.

SELECT   DEPT, 
   YEAR(DOB)  [COUNT], 
   COUNT(*)  [COUNT]   FROM   DBO.EMP   GROUP BY   DEPT, 
   YEAR(DOB)    UNION ALL   SELECT   DEPT, 
   NULL [COUNT], 
   COUNT(*)  [COUNT]   FROM   DBO.EMP   GROUP BY   DEPT   UNION ALL   SELECT   NULL DEPT, 
   YEAR(DOB)  [COUNT], 
   COUNT(*)  [COUNT]   FROM   DBO.EMP   GROUP BY   YEAR(DOB)    UNION ALL   SELECT   NULL DEPT, 
   NULL [COUNT], 
   COUNT(*)  [COUNT]   FROM   DBO.EMP  

The four possible combinations are.

Cube in SQL Server

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

Example

SELECT DEPT,YEAR(DOB) [YEAR],COUNT(*) [COUNT] FROM DBO.EMP   
GROUP BY CUBE((DEPT),(YEAR(DOB)))  

Rollup in SQL Server

The Rollup clause gives the hierarchy form result and generates the empty grouping set.

Example

SELECT   DEPT, 
   YEAR(DOB)  [YEAR], 
   COUNT(*)  [COUNT]   FROM   DBO.EMP   GROUP BY   ROLLUP(
     (DEPT), 
     (
       YEAR(DOB)   
  )   
)  

Here hierarchy means in the preceding example, three possible combinations exist.

(
  Dept, 
  Year(DOB)
) (Dept) ()

Grouping in SQL Server 

The Grouping function identifies the grouping set that accepts one input column; if that column is in that grouping set, 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 result is -1.

Example

0: This means it is part of the grouping set.

1: This means it is not part of the grouping set.

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

Examples of Actual Scenarios

The following is for a salary by department:

SELECT   DEPT, 
   SUM(SALARY)  AS [SALARY]   FROM   EMP   GROUP BY   DEPT WITH ROLLUP  --OR--  
SELECT   DEPT, 
   SUM(SALARY)  AS [SALARY]   FROM   EMP   GROUP BY   ROLLUP(DEPT)  

Conclusion

This article taught us about multiple grouping sets, grouping sets, Cubes, and Rollups with code examples in SQL Server. Continue reading about the Difference Between Grouping and Grouping_ID in SQL.