Re: Mathematical operations with NULL values

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 -----
From: Alexander Pucher
To: pgsql-general(at)postgresql(dot)org
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
--------------------------------------------------------