SQL group by with Rollup

/*
Create an example to demonstrate the effect of group by with Rollup
*/

-- Create a temporary table
Create Table # tmpa
(Dept char (3), sect char (3), line char (3), line_desc varchar (30), title_code char (3), title_desc varchar (30), headcount int
)

-- Add some data records
Insert # tmpa select 'da', 's1', 'La ', 'Line A', 't1', 'title DESC 1', 3
Insert # tmpa select 'da', 's1', 'La ', 'Line A', 't2', 'title DESC 2', 3
Insert # tmpa select 'da', 's1', 'lb', 'Line B ', 't1', 'title DESC 1', 3
Insert # tmpa select 'da', 's1', 'lb', 'Line B ', 't2', 'title DESC 2', 3
Insert # tmpa select 'da', 's2', 'La ', 'Line A', 't1', 'title DESC 1', 3
Insert # tmpa select 'da', 's2', 'lb', 'Line B ', 't1', 'title DESC 1', 3
Insert # tmpa select 'db', 's1', 'lb', 'Line B ', 't1', 'title DESC 1', 3
Insert # tmpa select 'db', 's2 ', 'La', 'Line A', 't1', 'title DESC 1', 3
Insert # tmpa select 'db', 's2', 'lb', 'Line B ', 't1', 'title DESC 1', 3
Insert # tmpa select 'dc', 's1', 'La ', 'Line A', 't1', 'title DESC 1', 3
Insert # tmpa select 'dc', 's2', 'lb', 'Line B ', 't1', 'title DESC 1', 3

Select * from # tmpa

-- Direct effect
Select Dept, sect, line, title_code, max (title_desc) as title_desc, sum (Headcount) as headcounts
From # tmpa
Group by dept, sect, line, title_code with Rollup

-- Effect of converting one of the headers
Select Dept, sect, line, (case when title_code is null then 'total (by line): 'else title_code end) as title_code, sum (Headcount) as headcounts
From # tmpa
Group by dept, sect, line, title_code with Rollup

-- Enhance the effect of converting one of the headers
Select Dept, sect, line,
(Case when (title_code is null) and (line is not null) Then 'total (by line): 'else title_code end) as title_code,
Sum (Headcount) as headcounts
From # tmpa
Group by dept, sect, line, title_code with Rollup

-- Process all the results
Select
(Case when (DEPT is null) Then 'Grant Total: 'else isnull (Dept, '') End) as sect,
(Case when (sect is null) and (DEPT is not null) Then 'total (by dept): 'else isnull (sect, '') End) as sect,
(Case when (line is null) and (Sect is not null) Then 'total (by sect): 'else isnull (line, '') End) as line,
(Case when (title_code is null) and (line is not null) Then 'total (by line): 'else isnull (title_code, '') End) as title_code,
Sum (Headcount) as headcounts
From # tmpa
Group by dept, sect, line, title_code with Rollup

-- Process all the results
Select
(Case when (DEPT is null) Then 'Grand Total: 'else isnull (Dept, '') End) as sect,
(Case when (sect is null) and (DEPT is not null) Then 'total (by dept: '+ dept +'): 'else isnull (sect, '') end) as sect,
(Case when (line is null) and (Sect is not null) Then 'total (by sect: '+ sect +'): 'else isnull (line, '') end) as line,
(Case when (title_code is null) and (line is not null) Then 'subtotal (by group: '+ LINE +'): 'else isnull (title_code, '') end) as title_code,
Sum (Headcount) as headcounts
From # tmpa
Group by dept, sect, line, title_code with Rollup

Select
(Case when (DEPT is null) Then 'Grant Total: 'else isnull (Dept, '') End) as sect,
(Case when (sect is null) and (DEPT is not null) Then 'total (by dept): 'else isnull (sect, '') End) as sect,
Max (line), max (title_code ),
Sum (Headcount) as headcounts
From # tmpa
Group by dept, sect with Rollup
 

Sect headcounts
------------------------------------------------------
Da S1 LB T2 12
Da S2 LB T1 6
Da total (by dept): LB T2 18
DB S1 LB T1 3
DB S2 LB T1 6
DB total (by dept): LB T1 9
DC S1 la T1 3
DC S2 LB T1 3
DC total (by dept): LB T1 6
Grant total: LB T2 33