CODEWITHSUNDEEP
postgresql
Wells
Wells

Reputation: 10969

postgreSQL: averaging non-null values

Seems simple, but... I have data like so:

pid   score1 score2 score3
1     1      3      2
2     3             1
3     4

I want to do an average score for the three only where there are non null values. Sort of like sum(score1+score2+score3)/3 but the denominator essentially needs to be a total of the non-null values for the given row, so 3 for pid 1, 2 for 2, and 1 for 3.

Is there a simple thing I'm missing here?

Upvotes: 0

Views: 95

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125424

with t(pid, score1, score2, score3) as (
    values (1,1,3,2), (2,3,null,1), (3,4,null,null)
)
select
    (sum(score1) + sum(score2) + sum(score3))::numeric / 
    (count(score1) + count(score2) + count(score3))
    as avg,
    avg(coalesce(score1, 0) + coalesce(score2, 0) + coalesce(score3, 0))
    as avg2
from t;
        avg         |        avg2        
--------------------+--------------------
 2.3333333333333333 | 4.6666666666666667

Upvotes: 1

Related Questions