October 8, 2007 at 11:10 pm
Hi All,
I have a race results database with about 15 tables. I need to count and sum the values in specific columns in two tables with about 500,000 rows in each table.
For example, I need to count and sum columns "Fast", "Good", "Slow", "FastWins", "GoodWins", "SlowWins", "Fast2nds", "Fast3rds", etc WHERE RunnerName = "XYZ"
I need to either return these values into another table or return them to my app for further processing.
I am using C# within VS2005, and I have attempted to use a SP which seems to me to be the better approach. Is there some way to use a single SP to return multiple results?
Can anyone provide a pointer in the right direction for me please?
Cheers,
Mark Chimes
October 8, 2007 at 11:42 pm
Hi
Yes using a SP would be the right approach in this case. you can return multiple recordsets in a sp.
Since you are dealing with large number of records make sure you have the correct indexes on the table to get better performance.
"Keep Trying"
October 8, 2007 at 11:51 pm
Thanks Chirag,
Could you provide a sample SP of how to address multiple columns for both SUM and COUNT at the same time? How do I return the multiple results?
cheers,
Mark
October 9, 2007 at 12:23 am
SELECT SUM(Col1), COUNT(Col1), COUNT(*) FROM Table1
WHERE Col2 = 'qwerty'
N 56°04'39.16"
E 12°55'05.25"
October 9, 2007 at 4:12 am
Hi Peter,
Thanks for the sample. What I need is something more like...
SELECT SUM(Col1), COUNT(Col1), COUNT(*)
FROM Table1
WHERE Col2 = 'qwerty' and Col3 = 1 and Col4 = "Fast"
AND SUM(Col1), COUNT(Col1)
FROM Table1
WHERE Col2 = 'qwerty' and Col3 = 2 and Col4 = "Fast"
AND SUM(Col1), COUNT(Col1)
FROM Table1
WHERE Col2 = 'qwerty' and Col3 = 3 and Col4 = "Fast"
AND SUM(Col1), COUNT(Col1)
FROM Table1
WHERE Col2 = 'qwerty' and Col3 = 1 and Col4 = "Good" ... and so on...
and then return all results in a form so I can individually process them.
Is this possible?
Currently, I have each of these seperated into their own scripts. If I continue with this model I will end up with over 30 almost identical SPs.
Cheers,
Mark Chimes
October 9, 2007 at 4:26 am
SELECT SUM(CASE WHEN Col2 = 'qwerty' AND Col3 = 1 AND Col4 = 'Fast' THEN Col1 ELSE 0 END) AS 'Option1',
SUM(CASE WHEN Col2 = 'good' AND Col3 = 5 AND Col4 = 'Fast' THEN Col1 ELSE 0 END) AS 'Option2',
SUM(CASE WHEN Col2 = 'peso' AND Col3 = 4 AND Col4 = 'Lightning' THEN Col1 ELSE 0 END) AS 'Option3',
SUM(CASE WHEN Col2 = 'peso' AND Col3 = 2 AND Col4 = 'Good' THEN Col1 ELSE 0 END) AS 'Option4',
SUM(CASE WHEN Col2 = 'what' AND Col3 = 1 AND Col4 = 'Faster' THEN Col1 ELSE 0 END) AS 'Option5'
FROM Table1
N 56°04'39.16"
E 12°55'05.25"
October 10, 2007 at 2:36 am
Hi
Peters script will work, but if you find it confusing try this. create a temp table or table data type and populate that with data that you want. Finally you can just select from the temp table.
If you are dealing with a large number of records then case might have performance issues.
"Keep Trying"
October 10, 2007 at 6:41 am
Wouldn't a simple query with grouping on Col2, COl3, and Col4 return the results you want, so you could process them in the C# application. Something like:
Select
Col2,
Col3,
Col4,
Sum(Col1) as Col1_Sum,
Count(Col1) as Col1_Count,
Count(*) as Total_Count
From
Table1
Group By
Col2,
Col3,
Col4
Order By
Col2,
Col3,
Col4
This code would return a result set like:
Col2 Col3 Col4 Col1_Sum Col1_Count Total_Count
---- ---- ---- --------- ---------- ------------
qwuerty 1 Fast 120 10 10
qwuerty 2 Fast 150 5 5
Now in the C# application you can work with results.
I am not sure what you are trying to get with the Count(*) as I would assume it returns the same count as the Count(Col1).
It would be easier to give a true solution if you posted the DDL for the tables. In this case I would assume that Col2 is Runner Name, Col3 is place (1st, 2nd, etc..) Col4 is race type ("Fast", "Slow", etc..).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 10, 2007 at 6:45 am
Count ( column) counts the number of non null values in the column. Count(*) counts all rows regardless of nulls.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2007 at 12:55 pm
It sounds like you have actual column "Fast", "Slow", etc. in different tables and you want a total count and summation from all those tables. Here, with just a few of your listed columns for brevity, is one way. All you're doing is getting the info from each table (count and sum) and merging into one dataset. Then processing all that info into one row (note that count becomes sum in that process because you're totaling the number of occurances).
select sum([NumFast]) as NumFast, sum ([SumFast]) as SumFast,
sum([NumGood]) as NumGood, sum ([SumGood]) as SumGood,
sum([NumSlow]) as NumSlow, sum ([SumSlow]) as SumSlow
from (
select count([Fast]) as NumFast, sum ([Fast]) as SumFast,
count([Good]) as NumGood, sum ([Good]) as SumGood,
count([Slow]) as NumSlow, sum ([Slow]) as SumSlow
from table1
WHERE RunnerName = @InParameter
group by RunnerName
union all
select count([Fast]) as NumFast, sum ([Fast]) as SumFast,
count([Good]) as NumGood, sum ([Good]) as SumGood,
count([Slow]) as NumSlow, sum ([Slow]) as SumSlow
from table2
WHERE RunnerName = @InParameter
group by RunnerName
) A;
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 11, 2007 at 6:45 am
Try this technique. It does not use an actual temp table but a 'dynamic or inline view' which is basically a table in memory.
SELECT COUNT(SUMS.SumFast), COUNT(SUMS.SumGood), COUNT(SUMS.SumSlow)
FROM (
SELECT SUM(Fast) AS SumFast, SUM(Good) AS SumGood, SUM(SLOW) AS SumSlow
FROM ActionTable
) AS SUMS
Good Luck
- Scott
October 14, 2007 at 9:32 pm
Hi All,
Thanks for all the suggestions. I have taken a mix and come up with a result that works well for me.
For those interested, I have column names of "RunnerName", "FinishPos", "Going" (Fast/Slow/Heavy).
So I needed to know:
- the total number of times each Runner has raced,
- the total number of times each runner had placed 1st, 2nd, 3rd,
- the total number of times each place was achieved fo each "Going"
Now that I have read thru all the replies, and understand SQL's capabilities a little better I see how easy this is.
Thanks again everyone.
Cheers,
Mark Chimes
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply