GROUPING in SQL Server

In this SQL Server tutorial, you will understand GROUPING in SQL Server, where you will learn the syntax of the GROUPING() function and its definition.

Then, using an example, you will apply the GROUPING() function on the table column.

Table of Contents

When you perform the aggregation on the table rows with the GROUP BY clause, it returns the row with the aggregated result divided into separated groups. If you want to identify whether a row is an aggregated result of a GROUP BY operation, then use the GROUPING function.

The syntax is given below.

GROUPING (column_name)

Where,

GROUPING(): This function tells whether a row is aggregated. It returns 1 if the row is an aggregate row and 0 if it is not aggregated.
column_name: Name of the column used in the GROUP BY clause, and this column is being evaluated.

For example, a FinancialInfo table with columns Year, Department, Revenue and Expenses is shown below.

GROUPING in SQL Server FinanceInfo Table

Suppose a company in the USA must generate a yearly summary of expenses and revenue by the department for financial reporting. For that, use the below query.

SELECT
	Year,
	CASE
    	WHEN GROUPING(Department) = 1 THEN 'Total'
    	ELSE Department
	END AS Department,
	SUM(Revenue) AS TotalRevenue,
	SUM(Expenses) AS TotalExpenses
FROM
	FinancialInfo
GROUP BY
	Year,
	ROLLUP (Department);

When you execute the above query, the result set contains a table showing each department’s total revenue and expenses per year, with subtotals for each year and a grand total at the end.

Let’s understand the query part WHEN GROUPING(Department) = 1 THEN ‘Total’ ELSE Department. Here, the GROUPING() function is used to differentiate between normal and aggregated rows by the ROLLUP.

If the row is aggregated using (GROUPING(department) =1), it labels the department as Total. Otherwise, it shows the actual department name. Here, you can see how the grouping function labels the row.
Then, the query SUM(Revenue) computes the total revenue for each group, and SUM(Expenses) computes the total expenses for each group.
After that, the query part GROUP BY Year, ROLLUP (Department), where data is grouped by Year and Department, then the ROLLUP() function is applied to the Department column. This means the query produces standard groupings, additional rows representing the subtotal of all departments for each year and a final row representing the grand total across all years and departments.

This is how to use the GROUPING in SQL Server to identify the aggregated.

Conclusion

In this SQL Server tutorial, you checked the rows to see whether it is an aggregated row or not using the GROUPING() function in SQL Server. Also learned how to use the grouping GROUPING() function on the table column.