the_hareeb
Replies to this thread:

More by the_hareeb
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Refresh page to view new replies
 SQL server roll-up function help
[VIEWED 11456 TIMES]
SAVE! for ease of future access.
Posted on 05-03-10 3:36 PM     Reply [Subscribe]
Login in to Rate this Post:     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

 
Posted on 05-03-10 3:37 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

My result should look something like this


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

 
Posted on 05-03-10 3:44 PM     Reply [Subscribe]
Login in to Rate this Post:     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

 
Posted on 05-03-10 3:44 PM     Reply [Subscribe]
Login in to Rate this Post:     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)

 
Posted on 05-03-10 3:51 PM     Reply [Subscribe]
Login in to Rate this Post:     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

 
Posted on 05-03-10 4:39 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

In Oracle,
SELECT … GROUP BY ROLLUP(grouping_column_reference_list)



 
Posted on 05-03-10 5:02 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

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

 
Posted on 05-03-10 5:12 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

by the way i am talking about php programming language

 
Posted on 05-03-10 7:43 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

oracle has diff syntax than sql server.


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

 
Posted on 05-03-10 9:24 PM     Reply [Subscribe]
Login in to Rate this Post:     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/


 
Posted on 05-03-10 9:49 PM     Reply [Subscribe]
Login in to Rate this Post:     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

 
Posted on 05-03-10 11:24 PM     Reply [Subscribe]
Login in to Rate this Post:     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?


 
Posted on 05-04-10 1:33 AM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

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

 
Posted on 05-04-10 8:42 AM     Reply [Subscribe]
Login in to Rate this Post:     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.
 
Posted on 05-04-10 8:51 AM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

this is what I want for my output

 
Posted on 05-04-10 11:15 AM     Reply [Subscribe]
Login in to Rate this Post:     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.


 
Posted on 05-04-10 11:26 AM     Reply [Subscribe]
Login in to Rate this Post:     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;


 
Posted on 05-04-10 11:58 AM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

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


 
Posted on 05-04-10 2:05 PM     Reply [Subscribe]
Login in to Rate this Post:     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 be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 200 days
Recommended Popular Threads Controvertial Threads
ChatSansar.com Naya Nepal Chat
TPS Re-registration case still pending ..
Toilet paper or water?
Anybody gotten the TPS EAD extension alert notice (i797) thing? online or via post?
Sajha Poll: Who is your favorite Nepali actress?
ढ्याउ गर्दा दसैँको खसी गनाउच
Problems of Nepalese students in US
nrn citizenship
TPS EAD auto extended to June 2025 or just TPS?
whats wrong living with your parents ?
Mamta kafle bhatt is still missing
Ajay Kumar Dev sentenced to 378 yrs
Why Americans reverse park?
Now Trump is a convicted criminal .
TPS advance parole Travel document i-131, Class of Admission ?
Tourist Visa - Seeking Suggestions and Guidance
Nepali Passport Renewal
मन भित्र को पत्रै पत्र!
Biden said he will issue new Employment visa for someone with college degree and job offers
To Sajha admin
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters