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!
CodePudding user response：
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;