On Aug 31, 2012, at 19:14, Thalis Kalfigkopoulos <tkalfigo@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
