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 |
|
shirotakakah
Starting Member
3 Posts |
 Posted - 2008-12-02 : 22:30:20
|
| Hello everyone.Is there a function similar to the AVG function to compute an average across variables and put it in a new variable, without doing the math by hand?I understand that I could do (var1 + var2 + var3)/3 as var4in my select statement. The problem is with missing values. If var1 and var2 have good values, but var3 is null, then 3 is not the right denominator, 2 should be. Further, var4 ends up as a Null value where any of the contributing variables is Null.Anyway, I'd appreciate any ideas. Regards Kent |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-02 : 23:35:03
|
| [code](coalesce(var1,0) + coalesce(var2,0) + coalesce(var3,0))*1.0/((case when var1 is null 0 else 1 end)+(case when var2 is null 0 else 1 end)+(case when var3 is null 0 else 1 end))[/code] |
 |
|
|
|
|
|
Averaging across variables