SQL average multiple columns for each row with nulls

Question

I have a table like this:

|Quality|Schedule|Cost Control|
-------------------------------
|7      | 8.5    |10          |
|NULL   | 9      |NULL        |

and I need to calculate the average of each row in the same table so it looks like this:

|Quality|Schedule|Cost Control|AVG|
----------------------------------
|7      | 8.5    |10          |8.5|
|NULL   | 9      |NULL        |9  |

which I have done using the following code:

SELECT r.Quality, r.Schedule, r.CostControl, 
((coalesce(r.quality,0)+
  coalesce(r.schedule,0)+
  coalesce(r.CostControl,0)/3) as Average
FROM dbo.Rating r

Which gives the following table:

|Quality|Schedule|Cost Control|AVG|
----------------------------------
|7      | 8.5    |10          |8.5|
|NULL   | 9      |NULL        |3  |

I know the problem is that the divisor is hard coded in my select statement, but I can't figure out how to make it variable. I tried using a case statement to select an addition column:

select Count(case when(r.quality) > 0 then 1 else 0 end + 
             case when (r.Schedule) > 0 then 1 else 0 end + 
             case when (r.CostControl) > 0 then 1 else 0 end)

But that only gives me one value. I'm out of ideas and facing a pretty tight deadline, so any help would be much appreciated.

Paul Abbott · Accepted Answer · 2018-06-25 18:08:39Z

4

Instead of dividing by 3, use

(CASE WHEN Quality IS NULL THEN 0 ELSE 1 END + 
 CASE WHEN Schedule IS NULL THEN 0 ELSE 1 END + 
 CASE WHEN [Cost Control] IS NULL THEN 0 ELSE 1 END)

answered Jun 25, 2018 at 18:08

Paul Abbott

7,2213 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DBA108642 Over a year ago

Had to throw in a Nullif to prevent dividing by 0 but otherwise this worked perfectly. Thanks!

Yogesh Sharma · Accepted Answer · 2018-06-25 18:32:22Z

2

I would use apply instead :

select *, (select sum(v) / count(v) 
           from ( values (quality), (Schedule), (CostControl) 
                ) tt(v) 
          ) as AVG
from table t;

edited Jun 25, 2018 at 18:32

answered Jun 25, 2018 at 18:06

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

1 Comment

DhruvJoshi Over a year ago

You can simply put avg now?

Gordon Linoff · Accepted Answer · 2018-06-25 19:29:38Z

2

I would use apply with avg():

SELECT r.Quality, r.Schedule, r.CostControl, v.average
FROM dbo.Rating r CROSS APPLY
     (SELECT avg(val)
      FROM (VALUES (quality), (schedule), (CostControl)) v(val)
     ) v(average);

This requires no subqueries, no long case expressions, generalizes easily to more columns, runs no risk of divide-by-zero . . . and the performance might even be equivalent to the case expression.

answered Jun 25, 2018 at 19:29

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Jason Roman Over a year ago

This seems to be the cleanest solution, tested and verified that it works.

Collectives™ on Stack Overflow

SQL average multiple columns for each row with nulls

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related