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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-06-07 : 07:16:02
|
| writes "select sum(price*quantity)as Cost from Orders where OrderNumber = 222 does not return the correct value when the same OrderNumber is present more than once. SqlServer seems to add all the price records then all the quantity records, and only then multiply the two together.select sum(distinct(price*quantity))as Cost from Orders where OrderNumber = 222 retursn the correct result, but only if there are truly unique price-quantity combinations.How do I retrieve the correct value, or how do I loop through the redordset for each OrderNumber ??" |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-07 : 07:31:51
|
quote:
Originally posted by AskSQLTeam
SqlServer seems to add all the price records then all the quantity records, and only then multiply the two together.
I'll go out on a limb and say that sum(p*q) = p1*q1+p2*q2+...pn*qn which conflicts with your analysis that the result is sum(p) * sum(q)I'm sure you have a problem, but I'm pretty sure your conclusions are wrong.Can you post some sample data and the result(s)? |
 |
|
|
|
|
|
Using the Sum function