SQL server roll-up function help

Posts 19 · Viewed 10087 · Go to Last Post

· Snapshot

Like · Likedby · 0

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..

Last edited: 03-May-10 03:37 PM

the_hareeb

· Snapshot

Like · Liked by · 0

My result should look something like this

Last edited: 04-May-10 08:51 AM

Khairey

· Snapshot

Like · Liked by · 0

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

newlynew

· Snapshot

Like · Liked by · 0

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)

the_hareeb

· Snapshot

Like · Liked by · 0

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

Gajedi

· Snapshot

Like · Liked by · 0

In Oracle,

SELECT … GROUP BY ROLLUP(grouping_column_reference_list)

raju161

· Snapshot

Like · Liked by · 0

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

raju161

· Snapshot

Like · Liked by · 0

by the way i am talking about php programming language

bhikari

· Snapshot

Like · Liked by · 0

oracle has diff syntax than sql server.

Last edited: 03-May-10 07:43 PM

hyperthread

· Snapshot

Like · Liked by · 0

Refer to the following, this may help you :

http://chiragrdarji.wordpress.com/2008/09/09/group-by-cube-rollup-and-sql-server-2005/
http://blog.sqlauthority.com/2010/02/24/sql-server-introduction-to-rollup-clause/

NEPAL_SACH22

· Snapshot

Like · Liked by · 0

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

Khairey

· Snapshot

Like · Liked by · 0

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?

raju161

· Snapshot

Like · Liked by · 0

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

the_hareeb

· Snapshot

Like · Liked by · 0

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

· Snapshot

Like · Liked by · 0

this is what I want for my output

STUPIDA

· Snapshot

Like · Liked by · 0

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.

panacea

· Snapshot

Like · Liked by · 0

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;

sanjeevstha

· Snapshot

Like · Liked by · 0

go
create table #hareeb
(ID varchar(10),
Name varchar(20),
Category varchar(10),
children int)

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')

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

the_hareeb

· Snapshot

Like · Liked by · 0

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

Please log in to reply to this post

You can also log in using your Facebook