Databases
StackOverflow2013
Postsdbo
POmysql: Average over multiple columns in one row, ignoring nulls
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql: Average over multiple columns in one row, ignoring nulls
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload