title: SQL average multiple columns for each row with nulls
body: <p>I have a table like this:</p>
  
  <pre><code>|Quality|Schedule|Cost Control|
  -------------------------------
  |7      | 8.5    |10          |
  |NULL   | 9      |NULL        |
  </code></pre>
  
  <p>and I need to calculate the average of each row in the same table so it looks like this:</p>
  
  <pre><code>|Quality|Schedule|Cost Control|AVG|
  ----------------------------------
  |7      | 8.5    |10          |8.5|
  |NULL   | 9      |NULL        |9  |
  </code></pre>
  
  <p>which I have done using the following code:</p>
  
  <pre><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
  </code></pre>
  
  <p>Which gives the following table:</p>
  
  <pre><code>|Quality|Schedule|Cost Control|AVG|
  ----------------------------------
  |7      | 8.5    |10          |8.5|
  |NULL   | 9      |NULL        |3  |
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>select Count(case when(r.quality) &gt; 0 then 1 else 0 end + 
               case when (r.Schedule) &gt; 0 then 1 else 0 end + 
               case when (r.CostControl) &gt; 0 then 1 else 0 end)
  </code></pre>
  
  <p>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.</p>