go
create table #hareeb
(ID varchar(10),
Name varchar(20),
Category varchar(10),
children int)
go
insert into #hareeb values (1,'John','Temp','2')
insert into #hareeb values (2,'Mary','Temp','4')
insert into #hareeb values (3,'Cindy','Temp','1')
insert into #hareeb values (4,'Yomesh','Perm','2')
insert into #hareeb values (5,'Yogesh','Perm','2')
go
select * from #hareeb where Category = 'Temp'
union
select '3SubTot = '+cast(a.SubTotal as varchar(2)),'--','--',null from
(
select name,category,COUNT(category) as SubTotal from #hareeb group by rollup(Category,Name)
) as a where a.Category = 'Temp' and Name is null
union
select * from #hareeb where Category = 'Perm'
union
select '6SubTot = '+cast(a.SubTotal as varchar(2)),'--','--',null from
(
select name,category,COUNT(category) as SubTotal from #hareeb group by rollup(Category,Name)
) as a where a.Category = 'Perm' and Name is null