COALESCE returns the leftmost non-null value. Perhaps what you wanted was sum(coalesce(b,0)) instead of coalesce(0,sum(b))
>>> On Tue, Dec 12, 2006 at 9:22 AM, in message <[EMAIL PROTECTED]>, Patrick Welche <[EMAIL PROTECTED]> wrote: > Is this a bug, or don't I understand coalesce()? > > create table test (a int, b int); > insert into test values (1,null); > insert into test values (2,1); > insert into test values (2,2); > select * from test; -- returns: > select sum(b) from test where a=1; -- null > select sum(b) from test where a=2; -- 3 > select coalesce(0,sum(b)) from test where a=1; -- 0 > select coalesce(0,sum(b)) from test where a=2; -- 0 > delete from test where a=1; > select coalesce(0,sum(b)) from test where a=2; -- 0 ! > > So when I use coalesce() with sum(), I always get the constant. I would > have expected it only in the case where sum() returns null.. > > What am I missing? ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly