SQL GROUPING_ID Function 2

SQL GROUPING_ID Function

The SQL GROUPING_ID is one of the Aggregate Functions, which is used to compute the level of Grouping. This GROUPING_ID function will return the integer value. The basic syntax behind this Grouping_Id in SQL Server is as shown below:

SELECT GROUPING_ID ([Column1, COlumn2,...,ColumnN)
FROM [Source]
GROUP BY Column1, COlumn2,...,ColumnN

Before we get into the practical example, let me show you the difference between GROUPING and GROUPING_ID

  • If we are computing the grouping on a single column then, the SQL Server GROUPING_ID and GROUPING functions will return the same output
  • If we are computing the grouping on multiple columns, the GROUPING_ID (Column1, Column2, Column3) = integer value of concatenated (GROUPING(Column1), (Column2), (Column3) result

For this grouping_id demo, We are going to use the [MyEmployees] table present in our Database. From the below figure you can observe that [MyEmployees] table has fourteen records

SQL GROUPING_ID Function 1

SQL Grouping_Id Function Example

The Grouping in SQL Server returns the level of grouping on the specified columns. In this SQL Server example, we will show you the same.

TIP: Please refer to the GROUP BY Clause to know the Grouping.

-- GROUPING_ID in SQL Server

SELECT [Education]
      ,[Occupation]
      ,SUM([YearlyIncome]) as income
      ,GROUPING([Education]) AS 'Edu Grouping'
      ,GROUPING([Occupation]) AS 'Occ Grouping'
      ,CAST(GROUPING([Education]) AS NVARCHAR(2)) +
         CAST(GROUPING([Occupation]) AS NVARCHAR(2)) AS 'Binary Grouping'
      ,GROUPING_ID([Education], [Occupation]) AS 'Grouping ID'
FROM [MyEmployees Table]
GROUP BY [Education]
        ,[Occupation] WITH ROLLUP
SQL GROUPING_ID Function 2

Within this SQL GROUPING_ID Function example, the following statements will check whether the Grouping performed on the Education, and Occupation columns are not by returning 1 and 0

 ,GROUPING([Occupation]) AS 'Grouping'
 ,GROUPING([Education]) AS 'Grouping 2'

Below Aggregate Function statement will concatenate the two grouping values (Grouping(Education) and Grouping(Occupation))

CAST(GROUPING([Education]) AS NVARCHAR(2)) +
         CAST(GROUPING([Occupation]) AS NVARCHAR(2)) AS 'Binary Grouping'

Next, we are finding the SQL Grouping Id of Education and Occupation. It is equal to the integer value of Binary Grouping.

GROUPING_ID([Education], [Occupation]) AS 'Grouping ID'

NOTE: We can convert the binary values to inter using 8421 pattern. For example, integer value of 10 = 2, 111 = 7, and 1011 = 11