Mathematical operations with NULL values
| От | Alexander Pucher |
|---|---|
| Тема | Mathematical operations with NULL values |
| Дата | |
| Msg-id | 416F95E8.60602@atlas.gis.univie.ac.at обсуждение исходный текст |
| Ответы |
Re: Mathematical operations with NULL values
|
| Список | pgsql-general |
Hi,
given a table with some data, e.g. some monthly measures. Some of the measures are missing though.
id m1 m2 m3 m4 m5 .... m12
----------------------------------------------
1 23 45 66 76 76 .... 12
2 76 NULL 77 88 77 ... 89
3 67 87 98 NULL 78 ... NULL
I would like the calculate the yearly average of each row, something like ((m1+m2+m3+m4+m5+...m12)/12). This would work if I had all montly values for one year. In the case of at least one NULL value involved, I would get NULL as result.
So instead of dividing each year by 12, I would have to divide by the number of measures available in each row.
Could someone point me to the correct SQL syntax for doing this.
Thanks a lot
alex.
given a table with some data, e.g. some monthly measures. Some of the measures are missing though.
id m1 m2 m3 m4 m5 .... m12
----------------------------------------------
1 23 45 66 76 76 .... 12
2 76 NULL 77 88 77 ... 89
3 67 87 98 NULL 78 ... NULL
I would like the calculate the yearly average of each row, something like ((m1+m2+m3+m4+m5+...m12)/12). This would work if I had all montly values for one year. In the case of at least one NULL value involved, I would get NULL as result.
So instead of dividing each year by 12, I would have to divide by the number of measures available in each row.
Could someone point me to the correct SQL syntax for doing this.
Thanks a lot
alex.
-- -------------------------------------------------------- Departement of Geography and Regional Research University of Vienna Cartography and GIS -------------------------------------------------------- Virtual Map Forum: http://www.gis.univie.ac.at/vmf --------------------------------------------------------
В списке pgsql-general по дате отправления: