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.
GROUPING in SQL Server
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(): 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.
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.
WHEN GROUPING(Department) = 1 THEN 'Total'
END AS Department,
SUM(Revenue) AS TotalRevenue,
SUM(Expenses) AS TotalExpenses
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.
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.
You may like to read:
- How to use MIN Function in SQL Server
- How to use MAX Function in SQL Server
- SQL Server STRING_AGG Function
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.