My result should look something like this Last edited: 04-May-10 08:51 AM

the_hareeb · May 3, 2010 3:37 PM

I am not into SQL now, but based on what I learned from school, I think it is: Select Category, sum (Category) as Subtotal from mytable group by Category; (don't know if keyword as should be there or not for MS SQL) Isn't it that easy ? Last edited: 03-May-10 03:44 PM

Khairey · May 3, 2010 3:44 PM

Here's an example. This should help. Table: Item Color Quantity -------------------- -------------------- -------------------------- Table Blue 124 Table Red 223 Chair Blue 101 Chair Red 210 This query generates a subtotal report: SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'             ELSE ISNULL(Item, 'UNKNOWN')        END AS Item,        CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'             ELSE ISNULL(Color, 'UNKNOWN')        END AS Color,        SUM(Quantity) AS QtySum FROM Inventory GROUP BY Item, Color WITH ROLLUP Item                 Color                QtySum                     -------------------- -------------------- -------------------------- Chair                Blue                 101.00                     Chair                Red                  210.00                     Chair                ALL                  311.00                     Table                Blue                 124.00                     Table                Red                  223.00                     Table                ALL                  347.00                     ALL                  ALL                  658.00                     (7 row(s) affected)

newlynew · May 3, 2010 3:44 PM

newlynew, i looked into that, it didnt help me. My category is not a number, it is a string so I cant use Sum function here directly khairey, i cannot use sum aggregate on nvarchar data type. we have to count the temp/perm values  use a rollup function here on category. I am not sure how Last edited: 03-May-10 03:52 PM

the_hareeb · May 3, 2010 3:51 PM

In Oracle,SELECT … GROUP BY ROLLUP(grouping_column_reference_list)

Gajedi · May 3, 2010 4:39 PM

Use for or while loop to convert each string into integer and add them up isn't that simple,

raju161 · May 3, 2010 5:02 PM

by the way i am talking about php programming language

raju161 · May 3, 2010 5:12 PM

oracle has diff syntax than sql server. Last edited: 03-May-10 07:43 PM

bhikari · May 3, 2010 7:43 PM

Refer to the following, this may help you : http://chiragrdarji.wordpress.... http://blog.sqlauthority.com/2...

hyperthread · May 3, 2010 9:24 PM

Try this out the_hareeb. select ID,name,Category, SUM(Children)as Total_Children from myBaseTable group by ID,name,Category with rollup   here is another one that gives you exactly what you wanted: Select Id, Name, Category=Case when ig=1 then 'SUBTOTAL' else Category end, Children FROM ( Select Id, Name, Category, Children=Sum(Children), ig=Grouping(Id), ng=Grouping(Name), cg=Grouping(Category) From myBaseTable Group by rollup (category,Id,Name) ) Grouped Where ng = ig and cg <> 1 Thanks! Last edited: 03-May-10 10:19 PM

Nepal_Sach22 · May 3, 2010 9:49 PM

my bad, how can I make sum on category  when it is nvarchar!!! :) I should have used count :) select category, count(category) as CategoryTotal , sum (children) as SubTotal from mytable group by category; ---- assuming children is integer. Does it work? If not, what does it actually result?

Khairey · May 3, 2010 11:24 PM

cg <> 1, Nepal_ sach, can you explain me why you wrote, cg <> 1 Sorry this condition is never true

raju161 · May 4, 2010 1:33 AM

Guys, my question was something different. I want how many are temporary and how many are permmant as my subtotals. not the number of children. sorry for the confusion.

the_hareeb · May 4, 2010 8:42 AM

this is what I want for my output

the_hareeb · May 4, 2010 8:51 AM

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.

STUPIDA · May 4, 2010 11:15 AM

If something like this works then use the following query. I am not sure if it works in SqlServer. Try to go through Analytical Functions, it will make jobs like this a lot easier. Thanks. select id,name, category,count(*) over (partition by category ) categroy_total_count, children from test1 order by id;

panacea · May 4, 2010 11:26 AM

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

sanjeevstha · May 4, 2010 11:58 AM

guys thank you very much for all your help. I appreciate all your time. I have found a work-around.

the_hareeb · May 4, 2010 2:05 PM