title: MS-SQL Average Columns with NULL
body: <p>So I've got 3 different columns (basket 1, 2, and 3). Sometimes these columns have all the information and sometimes one or two of them are null. I have another column that I'm going to average these values into and save.</p>
  
  <p>Is there a sleek/easy way to get the average of these three columns even if one of them is null? Or do I have to have a special check for each one being null?</p>
  
  <p>Example data( ~~ is null)</p>
  
  <pre><code>- B1 - B2 - B3 - Avg
  ------------------------------
  - 10 - 20 - 30 - 20
  - 10 - ~~ - 30 - 20
  - ~~ - 20 - ~~ - 20
  </code></pre>
  
  <p>How would I write the T-SQL to update my temp table?</p>
  
  <pre><code>UPDATE @MyTable
     SET Avg = ???
  </code></pre>
  
  <hr>
  
  <p>Answer:
  Thanks to Aaronaught for the method I used. I'm going to put my code here just in case someone else has the same thing.</p>
  
  <pre><code>WITH AverageView AS
  (
      SELECT Results_Key AS xxx_Results_Key,
              AVG(AverageValue) AS xxx_Results_Average
      FROM @MyResults
          UNPIVOT (AverageValue FOR B IN (Results_Basket_1_Price, Results_Basket_2_Price, Results_Basket_3_Price)) AS UnpivotTable
      GROUP BY Results_Key
  )   
  UPDATE @MyResults
      SET Results_Baskets_Average_Price = xxx_Results_Average
      FROM AverageView
      WHERE Results_Key = xxx_Results_Key;
  </code></pre>