Re: CASE/WHEN behavior with NULLS

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