Difference Between Grouping and Grouping_ID in SQL

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

  1. USE DEMOS  
  2.   
  3. --CREATE THE EMP TABLE  
  4. CREATE TABLE DBO.EMP  
  5. (  
  6.     ID INT IDENTITY(1,1) PRIMARY KEY,  
  7.     FIRTSNAME VARCHAR(100) ,  
  8.     LASTNAME VARCHAR(100),  
  9.     LOCATION VARCHAR(100),  
  10.     DOB DATETIME,  
  11.     SALARY MONEY,  
  12.     DEPT INT   
  13. )  
Insert data using the Merge statement:
  1.  --INSERT DATA USING MERGE STATEMENT  
  2. MERGE INTO DBO.EMP AS T_EMP  
  3. USING (VALUES  
  4. ('RAKESH','KALLURI','HYDERABAD','07-23-1989',24000,1),  
  5. ('NARESH','CH','PUNE','07-23-1987',48000,1),  
  6. ('SRUJAN','KUMAR','HYDERABAD','07-23-1988',25000,1),  
  7. ('VENKATESH','BODUPPALY','HYDERABAD','07-23-1986',32000,2),  
  8. ('ALI','MD','HYDERABAD','07-23-1987',38000,2),  
  9. ('GANGA','RAJAYAM','PUNE','05-26-1987',390000,2),  
  10. ('RAVI','KUMAR','CHENNAI','03-23-1986',47000,1),  
  11. ('PRAVEEN','KUMAR','DELHI','07-23-1988',33000,2)  
  12. AS S_EMP(FIRTSNAME,LASTNAME,LOCATION,DOB,SALARY,DEPT)  
  13. ON 1=2  
  14. WHEN NOT MATCHED  THEN INSERT(FIRTSNAME,LASTNAME,LOCATION,DOB,SALARY,DEPT)  
  15. VALUES(S_EMP.FIRTSNAME,S_EMP.LASTNAME,S_EMP.LOCATION,S_EMP.DOB,S_EMP.SALARY,S_EMP.DEPT);  
Check the Data
  1. --CHECK THE DATA   
  2. SELECT * FROM DBO.EMP  
table

Grouping

The Grouping function identifies the grouping set that accepts the one input column. If that column is in that grouping set it results 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
  1. --0-->MEANS THE PART OF GROUPING SET  
  2. --1--> MEANS THE NOT PART OF GROUPING SET  
  3.   
  4.   
  5. SELECT DEPT,GROUPING(DEPT) GRP_DEPT,YEAR(DOB) [YEAR],GROUPING(YEAR(DOB)) GRP_YEAR ,COUNT(*) [COUNTFROM DBO.EMP   
  6. GROUP BY ROLLUP ((DEPT), (YEAR(DOB)))  
Grouping

Grouping_ID

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

Examples

The following example shows the 2 columns input to Grouping_ID.

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

The following example shows the 3 columns input to Grouping_ID.
  1. SELECT DEPT,YEAR(DOB) [YEAR],LOCATION,GROUPING_ID(DEPT,YEAR(DOB),LOCATION) GRP_ID ,COUNT(*) [COUNTFROM DBO.EMP   
  2. GROUP BY ROLLUP((DEPT),(YEAR(DOB)),LOCATION)  
Grouping ID