Re: Mathematical operations with NULL values

On Fri, 15 Oct 2004, Najib Abi Fadel wrote:

> 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
(orany specified value in the second argument) : 
>
> select (coalesce(m1,0) + coalesce(m2,0) + ....... +coalesce(m12,0) ) /12

That's wrong, you should divide by the number of available measures,
not just by 12. NULL is not 0.

> ----- Original Message -----
>  From: Alexander Pucher
>  To: pgsql-general@postgresql.org
>  Sent: Friday, October 15, 2004 11:18 AM
>  Subject: [GENERAL] Mathematical operations with NULL values
>
[...]
>  So instead of dividing each year by 12, I would have to divide by the number of measures available in each row.

I can't think of any elegant solution.

As Richard already pointed out, you need either to rearrange your table
or write a procedure. You also may create a table when needed, and drop it
when done.

.TM.
--
       ____/  ____/   /
      /      /       /            Marco Colombo
     ___/  ___  /   /              Technical Manager
    /          /   /             ESI s.r.l.
  _____/ _____/  _/               Colombo@ESI.it