On Aug 31, 2012, at 19:14, Thalis Kalfigkopoulos <tkalfigo@gmail.com> wrote:
> Hello all,
>
> I have a query that presents a sum() where in some records it's NULL
> because all members of the group are NULL.
> I decided I wanted to see a pretty 0 instead of NULL since it fits the
> logic of the app.
>
> This didn't work as expected (the NULL's persisted):
> ...CASE sum(foo) WHEN NULL THEN 0 ELSE sum(foo) END...
Guessing this form effectively evaluates to
WHEN sum(foo) = NULL instead of IS NULL and thus the wrong answer:
>
> Whereas changing it to:
> ...CASE WHEN sum(foo) IS NULL THEN 0 ELSE sum(foo) END...
> it works as expected, substituting the sum()'s that are NULL to zeros.
>
> Is that expected behavior? Do i misunderstand how CASE/WHEN works?
>
Yes.
That said you might want to try
SUM(COALESCE(foo, 0))
or
SUM(case when foo is null then 0 else foo end)
Your current attempt does not handle mixed NULL and NOT NULL the way most people would want it to (though maybe you
do...)
> Running: PostgreSQL 9.1.3 on i686-pc-linux-gnu, compiled by gcc (GCC)
> 3.4.6, 32-bit
>
> TIA,
> Thalis K.
>
>
David J