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.