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

Question

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.

Does this answer your question? Strange MySQL AVG() anomaly NULL values — sumit
– sumit, Commented Dec 18, 2019 at 22:49
Pivot so your data is in a single column/multiple rows and use AVG. How you do that is DB dependent. What you have is a bad table design - it makes you have to do these things.... — LoztInSpace
– LoztInSpace, Commented Dec 18, 2019 at 22:50
Does this answer your question? SQL: sum 3 columns when one column has a null value? — ℛɑƒæĿᴿᴹᴿ
– ℛɑƒæĿᴿᴹᴿ, Commented Dec 18, 2019 at 22:52

ℛɑƒæĿᴿᴹᴿ · Accepted Answer · 2019-12-18 23:01:02Z

1

 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

edited Dec 18, 2019 at 23:01

answered Dec 18, 2019 at 22:50

ℛɑƒæĿᴿᴹᴿ

5,4765 gold badges42 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-12-18 22:49:23Z

0

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;

answered Dec 18, 2019 at 22:49

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

GMB · Accepted Answer · 2019-12-18 22:56:55Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related