CODEWITHSUNDEEP
sqlpostgresql
Dustin
Dustin

Reputation: 43

SQL - create column with row averages excluding null values

I have a table like this

name jan feb march april may
stan 3 null 7 null 3
dawn 2 3 9 2 null

and I'd like to ad a column that has averages rows while skipping nulls

name jan feb mar apr may AVG
stan 3 null 7 null 3 4.3
dawn 2 3 9 2 null 4

The following code results in a null value for all rows that are missing values

SELECT *, AVG(jan+feb+mar+apr+may)/5 as avg
FROM t

Thanks in advance!

Upvotes: 0

Views: 36

Answers (1)

Rajat
Rajat

Reputation: 5803

I would use arrays and then unnest it to calculate the avg

with cte as

(select  *, unnest(array[jan,feb,mar,apr,may]) as months
 from t)

 select name,jan, feb, mar, apr, may, avg(months) 
 from cte
 group by name, jan, feb, mar, apr, may;

Upvotes: 1

Related Questions