How can I select average of multiple columns?
Suppose I have some data like:
X Y Z
-------------
6 3 3
5 5 NULL
4 5 6
11 7 8
I want to get something like
AVG
-------------
4
5
5
8.66666667
I tried select avg(x, y, z) from table
But it doesn't work.
Any ideas on a query to do that?
Try
Select (Coalesce(x,0) + Coalesce(y,0) + Coalesce(z,0)) /
(Coalesce(x/x, 0) + Coalesce(y/y, 0) + Coalesce(z/z, 0))
or
Select (Coalesce(x,0) + Coalesce(y,0) + Coalesce(z,0)) /
(Case When x Is Null 0 Else 1 End +
Case When y Is Null 0 Else 1 End +
Case When z Is Null 0 Else 1 End)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With