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 (or any 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(at)postgresql(dot)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(at)ESI(dot)it