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

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

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

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

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.

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)