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 |
|
detlion1643
Yak Posting Veteran
67 Posts |
 Posted - 2010-06-17 : 10:37:22
|
| I am summing 5 fields for now (lets say field names are a,b,c,d,e) like this: (a+b+c+d+e). This works, but now I need to take an average of them. Using (a+b+c+d+e)/5 does not work in my case. I need to find a way that will only divide by the number of fields that are greater than 0.I.E. if a and c contain 0's, then it would be (a+b+c+d+e)/3 because averaging will not include 0's. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-06-17 : 10:52:49
|
| So I am going to be doing a bunch of nested case when statements? Like, case when a=0 then case when b=0 then etc... basically for every combination? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-06-17 : 10:59:11
|
| This is a shortcut:(a+b+c+d+e)/(sign(a)+sign(b)+sign(c)+sign(d)+sign(e))You'll have to wrap those in ABS() if any of those values could be negative. |
|
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-06-17 : 11:05:40
|
| rob - none of the values are negative. I tried the sign() stuff and came up with an error:Msg 8134, Level 16, State 1, Line 1Divide by zero error encountered.EDIT: looks like it happens when all the values are 0 - a,b,c,d,e all contain 0's it will error. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-06-17 : 11:22:12
|
| This is ugly looking but will avoid the divide-by-zero error:(a+b+c+d+e) / IsNull( NullIf(sign(a)+sign(b)+sign(c)+sign(d)+sign(e),0), 1)You'll get zero as a result. You can change it if you want, or pull out the IsNull() portion and get NULL for divide-by-zero. |
|
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-06-17 : 11:30:20
|
| I've def. seen uglier syntax before, but this does what I was looking for, much appreciated. Thanks! |
|
|
|
|
|
|
|
|
a few math complications