How to deal with null values while DIVIDING

Question

I have a simple looking problem but I don't know how to handle. I have tow columns which are filled by value or null.

I have to make an average of these like this:

1. if in both are values = (A+B)/2
2. if one is null then = A or B.

Is it possible to write it in different way then:

 case when a is not null and b is not null then....
 etc.

If I use a simple (a+b)/2 I get null in cases where one of values is null.

Answer 1

Probably the simplest way is to use outer apply with avg() because avg() ignores NULL values:

select v.avg_ab
from t outer apply
     (select avg(x) as avg_ab
      from (values (t.A), (t.B)
           ) v
     ) v;

You can also do this with a complicated case expression:

select (case when A is not NULL and B is not NULL then (A + B) / 2
             when A is not NULL then A
             when B is not NULL then B
        end) as avg_ab
. . .

This works well enough for 2 values; it is feasible for 3. It doesn't generalize well beyond that. Another way to use case is a bit more generalizable:

select ( (coalesce(A, 0) + coalesce(B, 0)) /
         ((case when A is not null then 1 else 0 end) +
          (case when B is not null then 1 else 0 end)
         )
       )

But the apply method is still simpler.

Answer 2

Assuming the case when they are both null should result in a null average, you can use the mathematical "trick" of (A+A)/2=A and use coalesce to write this in a pretty elegant fashion, IMHO:

(COALESCE(a, b) + COALESCE(b, a)) / 2

Answer 3

Try the following:

SELECT (ISNULL(a, b)+ISNULL(b, a))/2

Answer 4

This would be the cleanest solution

select  coalesce((A+B)/2,A,B)   

.
.
.

Demo:

declare @t table (id int,A int,B int)

insert into @t values (1,30,50),(2,30,null),(3,null,50),(4,null,null)

select  id,A,B,coalesce((A+B)/2,A,B) as result   
from    @t

---

+----+------+------+--------+
| id | A    | B    | result |
+----+------+------+--------+
| 1  | 30   | 50   | 40     |
+----+------+------+--------+
| 2  | 30   | NULL | 30     |
+----+------+------+--------+
| 3  | NULL | 50   | 50     |
+----+------+------+--------+
| 4  | NULL | NULL | NULL   |
+----+------+------+--------+