CODEWITHSUNDEEP
sql
bkarthik
bkarthik

Reputation: 119

Average of multiple columns in SQL when some data is not there

I have a SQL table that has 8 columns that are empirical results for the same process. However, some of the data is incomplete meaning that there are cases where some of the of the 8 columns have null instead of a numerical value. I know that if all 8 columns were present I could do something like ...

SELECT (val1+val2+val3+val4+val5+val6+val7+val8)/8 from mytable;

But if say two of the values are null what I want in that case is ...

SELECT (val1+val2+val3+val4+val5+val6)/6 from mytable;

How do I do this? The only other posts I have seen regarding this query (no pun intended) are to do with simply parsing out any null values.

Upvotes: 0

Views: 695

Answers (3)

ℛɑƒæĿᴿᴹᴿ
ℛɑƒæĿᴿᴹᴿ

Reputation: 5326

 SELECT sum(COALESCE(val1,0)
          + COALESCE(val2,0)
          + COALESCE(val3,0)
          + COALESCE(val4,0)
          + COALESCE(val5,0)
          + COALESCE(val6,0)
          + COALESCE(val7,0)
          + COALESCE(val8,0)) / 8 AS total FROM yourTable

Upvotes: 1

GMB
GMB

Reputation: 222432

One cross-database option uses coalesce() and case expressions. This assumes that not all of the values will be null on the same record.

select 
    (
        coalesce(val1, 0)
        + coalesce(val2, 0)
        + coalesce(val3, 0)
        + coalesce(val4, 0)
        + coalesce(val5, 0)
        + coalesce(val6, 0)
        + coalesce(val7, 0)
        + coalesce(val8, 0)
    ) / (
        case when val1 is null then 0 else 1 end
        + case when val2 is null then 0 else 1 end
        + case when val3 is null then 0 else 1 end
        + case when val4 is null then 0 else 1 end
        + case when val5 is null then 0 else 1 end
        + case when val6 is null then 0 else 1 end
        + case when val7 is null then 0 else 1 end
        + case when val8 is null then 0 else 1 end
    ) res
from mytable;

Another standard solution is to use union all and aggregation. Assuming that the primary key of your table is id, you would go:

select id, avg(val) res
from (
    select id, val1 val from mytable
    union all select id, val2 val from mytable
    union all select id, val3 val from mytable
    union all select id, val4 val from mytable
    union all select id, val5 val from mytable
    union all select id, val6 val from mytable
    union all select id, val7 val from mytable
    union all select id, val8 val from mytable
) t
group by id

The second solution actually highlights the fact that you should fix your data model to store the values in rows, not in columns.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You would probably be better off putting the data in separate rows. You can do this with some arithmetic:

SELECT (coalesce(val1, 0) + coalesce(val2, 0) + . .  .) /
       (case when val1 is not null then 1 else 0 end +
        case when val2 is not null then 1 else 0 end +
        . . .
       ) as average
FROM mytable;

If you database supports lateral joins (such as SQL Server, Oracle, and Postgres), then syntax such as this is simpler:

select t.*, v.average
from t cross join lateral
     (select avg(v.val) as average
      from (values (val1), (val2), . . . ) v(val)
     ) v;

Upvotes: 0

Related Questions