Re: Mathematical operations with NULL values - Mailing list pgsql-general

From Najib Abi Fadel
Subject Re: Mathematical operations with NULL values
Date
Msg-id 00a801c4b2a8$ecadcec0$f664a8c0@najib
Whole thread Raw
In response to Mathematical operations with NULL values  (Alexander Pucher <pucher@atlas.gis.univie.ac.at>)
Responses Re: Mathematical operations with NULL values
List 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 by date:

Previous
From: Richard Huxton
Date:
Subject: Re: Mathematical operations with NULL values
Next
From: "Greg Wickham"
Date:
Subject: Changing session ownership in a web app (or how to peel an onion)