How to calculate the Average of fields that are not zero in SQL Server?

Question

I have a few columns: Week1, Week2, Week3, Week4, Week5

i.e.

Week1 = 5
Week2 = 0
Week3 = 10
Week4 = 7
Week5 = 0

How do I calculate the average based on the fields that are not zero?

Incorrect: (5+0+10+7+0)/5 = 4.4

Correct: (5+0+10+7+0)/3 = 7.3

How to let create a custom column to let the SQL know that I only want to divide by 3 instead of 5?

T-SQL. i'm thinking of using AVG(CASE something) but not sure. — user3486647, May 28 '15 at 22:53
More specifically, do you want, in 1 row, the average of the 5 columns Week1 - Week5? — CDC, May 28 '15 at 22:55
possible duplicate of [TSQL - Average of all values in a column that are not zero](http://stackoverflow.com/questions/4751772/tsql-average-of-all-values-in-a-column-that-are-not-zero) — tjeloep, May 28 '15 at 23:23

score 3 · Accepted Answer · edited Jul 27 '17 at 10:47

3

If one or more columns are not zero, then they are averaged as you expected.
To obtain a null when all are zero:

select (week1+week2+week3+week4+week5)/
  nullif(
    case when week1=0 then 0 else 1 end +
    case when week2=0 then 0 else 1 end +
    case when week3=0 then 0 else 1 end +
    case when week4=0 then 0 else 1 end +
    case when week5=0 then 0 else 1 end,
  0)
from ....

To obtain a zero when all are zero:

select 
  coalesce((week1+week2+week3+week4+week5)/
  nullif(
    case when week1=0 then 0 else 1 end +
    case when week2=0 then 0 else 1 end +
    case when week3=0 then 0 else 1 end +
    case when week4=0 then 0 else 1 end +
    case when week5=0 then 0 else 1 end,
  0),0)
from ....

And if you want to be safe with null on columns:

select 
  coalesce((
    coalesce(week1,0) +
    coalesce(week2,0) +
    coalesce(week3,0) +
    coalesce(week4,0) +
    coalesce(week5,0) 
  )/
  nullif(
    case when coalesce(week1,0)=0 then 0 else 1 end +
    case when coalesce(week2,0)=0 then 0 else 1 end +
    case when coalesce(week3,0)=0 then 0 else 1 end +
    case when coalesce(week4,0)=0 then 0 else 1 end +
    case when coalesce(week5,0)=0 then 0 else 1 end,
  0),0)
from ....

edited Jul 27 '17 at 10:47

David Andrei Ned

799
1
11
28

answered May 28 '15 at 22:55

Saic Siquot

6,513
5
34
56

Won't it result in a divide by zero overflow if all columns are 0? You'd probably want to add a NULLIF(..., 0) to the bottom. – CDC May 28 '15 at 22:57
but if all columns are 0 it will then result NULL. i think it's better to replace 0 with 1 to result 0. – tjeloep May 28 '15 at 23:14
@tjeloep, I'm not sure what is better to OP, so I give that option as an alternative on the answer – Saic Siquot May 28 '15 at 23:16
coalesce in each case? – user3486647 May 28 '15 at 23:34
is it possible to convert it to Access or do i have to create a separate question? – user3486647 May 29 '15 at 19:30
yes, to be clean, do a new question. I give a hint here: you can replace nullif, coalesce and case when with `IIF` access function. – Saic Siquot May 30 '15 at 13:54

score 3 · Answer 2 · answered May 29 '15 at 00:46

As a note, these types of problems (aggregation within a row) are often more easily expressed using apply:

select a.average
from table t cross apply
     (select avg(nullif(val, 0)) as average
      from (values (t.week1),
                   (t.week2),
                   (t.week3),
                   (t.week4),
                   (t.week5)
           ) v(val)
     ) a;

How to calculate the Average of fields that are not zero in SQL Server?

2 Answers2