SQL Server: Grouping ID Example

Let us suppose you have used the rollup operator to generate summary information along with the Details information and you now want to differentiate between summary and detail. You can make use of the SQL Server grouping_id function. Let us understand the GROUPING_ID function using some code.

Consider the following example:

declare @t table(region varchar(100), name varchar(100), amount decimal(12,2))
insert into @t
select 'region1','name1',2000 union all
select 'region1','name2',100 union all
select 'region1','name3',500 union all
select 'region1','name3',3400 union all
select 'region2','name1',3233 union all
select 'region2','name2',5000 union all
select 'region2','name2',5344 union all
select 'region3','name1',1200 union all
select 'region3','name2',900 union all
select 'region4','name1',2540
select region,name,SUM(amount) as amount,GROUPING_id(name) as [grouping] from @t
group by region, name
with rollup

Look at the result set below. The last column grouping generates 0 or 1. The rows with 0’s are Details whereas the rows with 1’s, contains Summary.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan