Difference Between Grouping and Grouping_ID in SQL

Introduction

In my previous article series, we saw the Multiple Grouping Sets in SQL- SERVER. Today I show the difference between Grouping and Grouping_ID.

Demo

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   
)  

Insert data using the Merge statement:

 --INSERT DATA USING MERGE STATEMENT  
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

--CHECK THE DATA   
SELECT * FROM DBO.EMP  

Grouping in SQL

The Grouping function identifies the grouping set that accepts the one input column. If that column is in that grouping set it results in an out-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 it results out -1.

Example

--0-->MEANS THE PART OF GROUPING SET  
--1--> MEANS THE NOT PART OF 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)))  

Grouping_ID in SQL

This is another function to identify the Grouping set using Grouping_ID and also computes the level of Grouping. Grouping_ID can be used in only Select, Having, and Order by clauses. This function accepts the multiple input columns and returns the integer value.

The Value 0 represents an element that is part of the grouping set, and 1 indicates that the element is not part of the grouping set.

For more information about Grouping_ID.

Here in this function, we can perform some binary calculations.

8 4 2 1

• If the Grouping_ID contains 2 columns we can consider -- 2 1 Binary calculation.
• If the Grouping_ID contains 3 columns we can consider –4 2 1 Binary calculation.
• If the Grouping_ID contains 4 columns we can consider –8 4 2 1 Binary calculation.
• If the Grouping_ID contains 5 columns we can consider –16 8 4 2 1 Binary calculation...and so on.

Example

The following example shows the 2 columns input to Grouping_ID.

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

The following example shows the 3 columns input to Grouping_ID.

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

 

Conclusion

This article taught us the difference between Grouping and Grouping_ID in SQL with code examples. Find more about Grouping and Grouping_ID in SQL Server.