ROLLUP in SQL Server

I learn about ROLLUP function today and I found it really usefull for generating reports. Let me give you one example where we can use ROLLUP. Consider We have a table as below

But the User wants the results as below. ie.,


User wants the Grand Total of the two columns(Offshore & Onsite) as the last row in the table. Here, we need the help of ROLLUP Keyword. Code Snippets to create table and inserting records
GO
create table #test
(roleplayer varchar(50),offshore int, onsite int)
GO
insert into #test
select 'Mobile',3,4
union
select 'Accomodation',43,4
union
select 'ID Card',30,41
union
select 'Payroll',31,42
union
select 'Library',23,42
union
select 'Bus',13,14
GO
select * from #test

Try the below query which will helps to produce the expected results

SELECT CASE
WHEN GROUPING(ROLEPLAYER)=1 THEN 'GRAND TOTAL'
ELSE ROLEPLAYER
END AS RolePlayer,
SUM(OFFSHORE) As OffShore,SUM(ONSITE) AS Onsite
FROM #TEST
GROUP BY ROLEPLAYER WITH ROLLUP

The above Query will produce the results as referred in the figure 2
Here is an another example for the ROLLUP.We have four columns for a table as below.

The User wants to display the subtotal for each roleplayer and the Grand total for all the Roleplayer as the last row. Refer the below figure


With the help of the ROLLUP Keyword we can achieve this.Code Snippet to Create table and inserting records
create table #test1
(roleplayer varchar(50),Area varchar(50),offshore int, onsite int)
GO
insert into #test1
select 'Mobile','Chennai',3,4
union
select 'Mobile','Bangalore',5,9
union
select 'Mobile','Calcutta',7,4
union
select 'Accomodation','Calcutta',43,4
union
select 'Accomodation','Cochin',43,4
union
select 'Accomodation','Chennai',43,4
union
select 'Accomodation','Mumbai',43,4
GO
select * from #test1

The below Query with ROLLUP produce the results as shown in the figure 4.SELECT CASE
WHEN GROUPING(ROLEPLAYER)=1 THEN ''
ELSE ROLEPLAYER
END AS RolePlayer,
CASE
WHEN GROUPING(Area)=1 THEN ISNULL(ROLEPLAYER +'Total','Grand Total')
ELSE Area
END AS Area,
SUM(OFFSHORE) AS OffShore,SUM(ONSITE) AS OnSite
FROM #TEST1
GROUP BY ROLEPLAYER,Area WITH ROLLUP
Thanks

I hope the ROLLUP will help you Guys.