13

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

Jared
  • 878
  • 3
  • 11
  • 19

1 Answers1

31

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    @Gordon....won't that bomb if value is null since it will then divide by zero? Or what happens when value is null – MikeTWebb Mar 01 '13 at 18:48
  • 1
    @MikeTWebb . . . NULL in *any* operation other than `IS NULL` and `IS NOT NULL` returns NULL. That is even true for divide by 0. But, I included the second version in case someone might be thinking about that. It will more clearly return NULL if nothing matches. – Gordon Linoff Mar 01 '13 at 18:52
  • This looks great! Can I do this for 85 different columns in one table that each have different NULL values? – Jared Mar 02 '13 at 00:08
  • @Jared . . . Yes you can. I would suggest that you copy the weight and value variable names into Excel, and generate the SQL code there. Much easier to copy formulas in Excel than to type them. – Gordon Linoff Mar 02 '13 at 00:20