Re: Mathematical operations with NULL values

Поиск
Список
Период
Сортировка
От Najib Abi Fadel
Тема Re: Mathematical operations with NULL values
Дата
Msg-id 00a801c4b2a8$ecadcec0$f664a8c0@najib
обсуждение исходный текст
Ответ на Mathematical operations with NULL values  (Alexander Pucher <pucher@atlas.gis.univie.ac.at>)
Ответы Re: Mathematical operations with NULL values
Список pgsql-general
Дерево обсуждения
You can replace Null values by the and make the defaut Value 0 !
 
If u can't change the Data in the database you can use the coalesce function which replaces the Null value by zero (or any specified value in the second argument) :
 
select (coalesce(m1,0) + coalesce(m2,0) + ....... +coalesce(m12,0) ) /12
 
----- Original Message -----
Sent: Friday, October 15, 2004 11:18 AM
Subject: [GENERAL] Mathematical operations with NULL values

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.

-- 
-------------------------------------------------------- 
Departement of Geography and Regional Research
University of Vienna
Cartography and GIS
--------------------------------------------------------
Virtual Map Forum: http://www.gis.univie.ac.at/vmf
--------------------------------------------------------

В списке pgsql-general по дате отправления: