SQL GROUPING_ID

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 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. Explore the SQL GROUPING function.

  • 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 the [MyEmployees] table has fourteen records

SQL GROUPING ID Function 1

SQL Grouping_Id Function Example

The Grouping_ID returns the level of grouping on the specified columns. In this SQL Server example, we will show you the same. See the list of SQL Aggregate Functions from the SQL Programming page.

TIP: Please refer to the SQL GROUP BY clause to know the Grouping.

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

The below Aggregate Function statement will concatenate the two grouping values (Grouping(Education) and Grouping(Occupation)). Check the SQL SUM function to calculate the total, and the SQL CAST function for data type conversion.

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

Next, we are finding the 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 the 8421 pattern. For example, integer value of 10 = 2, 111 = 7, and 1011 = 11

Categories SQL