Postgres Pro
Downloads
Home   >   mailing lists

Mathematical operations with NULL values - Mailing list pgsql-general

From Alexander Pucher
Subject Mathematical operations with NULL values
Date
Msg-id 416F95E8.60602@atlas.gis.univie.ac.at
Whole thread Raw
Responses Re: Mathematical operations with NULL values  (Richard Huxton <dev@archonet.com>)
List pgsql-general
Tree view
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: Mark Gibson
Date:
Subject: Re: Cache lookup failed for relation, when trying to DROP
Next
From: "Net Virtual Mailing Lists"
Date:
Subject: Tsearch2 trigger firing...