GROUPING_ID Function in SQL Server

In this SQL Server tutorial, you will learn about the GROUPING_ID function in SQL Server to identify the rows where the aggregation operation is applied.

You will understand the GROUPING_ID() function with its syntax and know how it works through an example.

Table of Contents

GROUPING_ID Function in SQL Server

The GROUPING_ID function in SQL Server is used with a query that contains a GROUP BY clause. Furthermore, it helps identify the grouping level in a query that uses ROLLUP or CUBE with a GROUP BY clause.

In other words, GROUPING_ID assigns a unique number to each row to identify the difference between the normal and aggregated, subtotal, or summary rows.

The syntax is given below.

GROUPING_ID ( column_name1, column_name2, ... )

Where,

GROUPING_ID(): The function identifies the rows grouping level.
column_name1, column_name2, ..: These are the columns used in the GROUP BY clause in your query.

If you don’t understand, then don’t worry. You will realise soon.

For example, suppose you have a FinancialInfo table with columns Year, Department, and Revenue, Expenses, as shown below.

GROUPING_ID Function in SQL Server Financialinfo Tables

Using the above table, you have to generate a report that shows total expenses by year and department, along with subtotals for each year and a total. For that, use the below query.

SELECT Year, Department,
      SUM(Expenses) AS TotalExpenses,
	  GROUPING_ID(Year, Department) AS GroupingLevel
FROM FinancialInfo
GROUP BY ROLLUP(Year, Department);

From the above output, the result set contains the expenses for each department and the total expenses for each year. The GroupingLevel column contains the value generated by the GROUPING_ID() function to differentiate between these different levels of data.

In the column GroupingLevel, values 0 and 1 are assigned; for example, the total expense for each department in 2020 is 0, but the total expenses for 2020 have a grouping level of 1.

Let’s understand the query part, SUM(Expenses) AS TotalExpenses, computes the total expenses for each group of year and department. It adds all the expenses and marks them as TotalExpenses.

Then GROUPING_ID(Year, Department) AS GroupingLevel uses the GROUPING_ID() function to identify the level of grouping for each row in the results and assigns a unique number to different groupings of year and department, which helps in understanding if a particular row is showing data for a specific department.

After that, GROUP BY ROLLUP(Year, Departement); in this part of the query, data grouping happens when the ROLLUP function is used to generate a summary of rows.

Then ROLLUP(Year, Departement) first groups the rows by year, and then within each year, it further groups the data by department. This grouping creates several levels of data: individual expenses within each year, totals for each year, and totals for all years and departments.

That happens when you execute the query, but you must understand how the GROUPING_ID function works here.

As you can see in the GroupingLevel column, a bit is assigned, such as 0 and 1. Each column bit is set to 1 if the column is aggregated (part of subtotal or total) and 0 if not. This function returns the decimal equivalent of this binary number.

This is how to use the GROUPING_ID function in SQL Server.

Conclusion

In this SQL Server tutorial, you learned how to use the GROUPING_ID function in SQL Server to differentiate between regular and aggregated rows. Then, with an example, you learned how the GROUPING_ID function works.