Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2004-06-08 : 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 - intMilkCost - moneyUserID - inttblLogMilk---------LogMilkID - intLogMilkCostTotal - moneyLogMilkCostAvg - moneyLogMilkDate - datetimeUserID - inttblUser-------UserID - intI 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
1456 Posts |
Posted - 2004-06-08 : 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 UserIDOS |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-06-08 : 08:23:21
|
No Loops PleaseDelete From dbo.tblLogMilkInsert 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, MilkIDJimUsers <> Logic |
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2004-06-13 : 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. |
|
|
|
|
|
|
|