| Author |
 Topic  |
|
|
Huligan
Yak Posting Veteran
USA
66 Posts |
 Posted - 06/08/2004 : 08:00:09
|
Hello. I need to create a stored procedure that (I think) needs a dynamic loop or a loop on all records with a sort. Here are the tables involved.
tblMilk
-------
MilkID - int
MilkCost - money
UserID - int
tblLogMilk
---------
LogMilkID - int
LogMilkCostTotal - money
LogMilkCostAvg - money
LogMilkDate - datetime
UserID - int
tblUser
-------
UserID - int
I need a stored procedure to grab all records (thousands) from tblMilk. I need to group together the records by UserID. For each group of records, I need to calculate a sum and average for MilkCost and save those values to tblLogMilk with the UserID.
I know how to do this in ASP, but I'm struggling with finding the syntax I need. In ASP, I would sort the records by UserID and loop through the records. Everytime the UserID changes, I would perform my calculations, save them to tblLogMilk and reset the variables used to total the values from tblMilk.
Is this the way it should be done in a stored procedure or is there a better way? Either way, I would appreciate some syntax suggestions. Thanks.
Huligan |
|
|
mohdowais
Sheikh of Yak Knowledge
United Arab Emirates
1456 Posts |
Posted - 06/08/2004 : 08:21:30
|
Do you have Books Online (the SQL Server User Manual)? Look up GROUP BY.
Your query would be similar to:
SELECT UserID, SUM(MilkCost), AVG(MilkCost) FROM tblMilk GROUP BY UserID
OS |
 |
|
|
JimL
SQL Slinging Yak Ranger
USA
1530 Posts |
Posted - 06/08/2004 : 08:23:21
|
No Loops Please
Delete
From dbo.tblLogMilk
Insert into dbo.tblLogMilk
(logUserid,logMilkID,logMilkCostTotal,logMilkCostAvg)
Select UserID as logUserid, milkid as logMilkID, Sum(Milkcost) As logMilkCostTotal, Avg(Milkcost) AS logMilkCostAvg
From dbo.tblmilk
Group By UserID, MilkID
Jim
Users <> Logic |
Edited by - JimL on 06/08/2004 13:42:18 |
|
|
|
Huligan
Yak Posting Veteran
USA
66 Posts |
Posted - 06/13/2004 : 09:08:47
|
| Thanks for the help Jim. The only problem I had was the INSERT statement kept creating errors. Then I saw you edited your post and the correction took care of my error. Thanks again. |
|
|
| |
Topic |
|
Stored procedure: Dynamic loop? Loop with sort?
