[VIEWED 11457
TIMES]
|
SAVE! for ease of future access.
|
|
|
the_hareeb
Please log in to subscribe to the_hareeb's postings.
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
|
|
|
|
the_hareeb
Please log in to subscribe to the_hareeb's postings.
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
|
|
|
Khairey
Please log in to subscribe to Khairey's postings.
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
|
|
|
newlynew
Please log in to subscribe to newlynew's postings.
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:
|
|
|
the_hareeb
Please log in to subscribe to the_hareeb's postings.
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
|
|
|
Gajedi
Please log in to subscribe to Gajedi's postings.
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)
|
|
|
raju161
Please log in to subscribe to raju161's postings.
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,
|
|
|
raju161
Please log in to subscribe to raju161's postings.
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
|
|
|
bhikari
Please log in to subscribe to bhikari's postings.
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
|
|
|
hyperthread
Please log in to subscribe to hyperthread's postings.
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/
|
|
|
NEPAL_SACH22
Please log in to subscribe to NEPAL_SACH22's postings.
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
|
|
|
Khairey
Please log in to subscribe to Khairey's postings.
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?
|
|
|
raju161
Please log in to subscribe to raju161's postings.
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 <> 1Sorry this condition is never true
|
|
|
the_hareeb
Please log in to subscribe to the_hareeb's postings.
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.
|
|
|
the_hareeb
Please log in to subscribe to the_hareeb's postings.
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
|
|
|
STUPIDA
Please log in to subscribe to STUPIDA's postings.
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.
|
|
|
panacea
Please log in to subscribe to panacea's postings.
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;
|
|
|
sanjeevstha
Please log in to subscribe to sanjeevstha's postings.
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
|
|
|
the_hareeb
Please log in to subscribe to the_hareeb's postings.
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.
|
|