Hi, trying to do this in SQL server…

I want to count how many records I have in each category(Temp and Perm). I need to use rollup function but I am getting errors. 

This is my base table..

Here's an example. This should help.

Table:

This query generates a subtotal report:

This is a SQL display issue. If u want the results exactly the way you have posted then .....rollup will not be a help. It cannot display results the way you have posted.

If u want that way. create temp tbls and do union to the result sets.

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