[GENERAL] Mathematical operations with NULL values

[Alexander Pucher]

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 --------------------------------------------------------