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)
  ), 
   ()  
);