Creating a Weighted Average - Dropping Weights for NULL values

Question

So there's the SQL Function AVG(), which takes the average of all values in a column, ignoring all NULL values. If one needs to make a weighted average, then they'd just use SUM(value * weight)/SUM(weight) with a Group By clause.

If I would want to do the latter, but some of my values are NULL, then how would I go about telling SQL to ignore weights with NULL value observations in the SUM(weight) function?

My other issue is that I'm taking an average of 90 different columns at once, so I'd like to avoid making 90 new weight variables for this calculation.

Let me know if I've made this clear or not.

I'm using SQL Server 2005

Answer 1

You would use conditional summing as the denominator:

select sum(value*weight) / sum(case when value is not null then weight else 0 end)

If the weights are always bigger than 0, then you don't have to worry about divide by 0. That would only occur when all the values are NULL. And, in that case the numerator would be NULL.

You could also phrase it as:

select sum(value*weight) / sum(case when value is not null then weight end)

or as:

select sum(case when value is not null then value*weight end) / sum(case when value is not null then weight end)

This is more verbose, but makes it very clear that you are ignoring NULL values in both the numerator and denominator.