title: mysql: Average over multiple columns in one row, ignoring nulls
body: <p>I have a large table (of sites) with several numeric columns - say a through f. (These are site rankings from different organizations, like alexa, google, quantcast, etc. Each has a different range and format; they're straight dumps from the outside DBs.)</p>
  
  <p>For many of the records, one or more of these columns is null, because the outside DB doesn't have data for it. They all cover different subsets of my DB.</p>
  
  <p>I want column t to be their weighted average (each of a..f have static weights which I assign), ignoring null values (which can occur in any of them), except being null if they're all null. </p>
  
  <p>I would prefer to do this with a simple SQL calculation, rather than doing it in app code or using some huge ugly nested if block to handle every permutation of nulls. (Given that I have an increasing number of columns to average over as I add in more outside DB sources, this would be exponentially more ugly and bug-prone.)</p>
  
  <p>I'd use AVG but that's only for group by, and this is w/in one record. The data is semantically nullable, and I don't want to average in some "average" value in place of the nulls; I want to only be counting the columns for which data is there.</p>
  
  <p>Is there a good way to do this?</p>
  
  <p>Ideally, what I want is something like <code>UPDATE sites SET t = AVG(a*@a_weight,b*@b_weight,...)</code> where any null values are just ignored and no grouping is happening.</p>
  
  <p>EDIT: What I ended up using, based on van's and adding in correct weighted averages (assuming that <code>a</code> has already been normalized as needed, in this case to a float 0-1 (1 = better):</p>
  
  <pre><code> UPDATE sites 
   SET t = (@a_weight * IFNULL(a, 0) + ...) / (IF(a IS NULL, 0, @a_weight) + ...) 
   WHERE (IF(a IS NULL, 0, 1) + ...) &gt; 0
  </code></pre>