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 |
phoenix22
Starting Member
20 Posts |
Posted - 2004-11-04 : 23:26:09
|
Hi, Is there a way to calculate a weighted average in SQL? If so, could you please provide the SQL, thank you in advance for your time and help. |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-04 : 23:36:13
|
check AVG, RollUp in BOL --------------------keeping it simple... |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-11-05 : 01:38:42
|
If what you are weighting by is a column in the table you can do it like this: select sum(amount * volume) / sum(volume) as WeightedAverage from mytableIf you are weighing by some time or duration (which is common) you may have to calculate elapsed times then use the same formula:select sum(amount * volume) / sum(volume) from ( select datediff (ss, time, (select top 1 time from mytable where time > o.time order by time) ) volume, amount from mytable o) x--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
|
|