coalesce and aggregate functions

Patrick Welche 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..

Coalesce returns the first non-null argument. In your example, 0 is
always the first non-null argument. You should be doing this instead:

select coalesce(sum(b),0) from test where a=2;

to get the desired effect.

BTW: This type of questions really belong to pgsql-general or
pgsql-novice, this list is for discussing development of PostgreSQL itself.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

"Patrick Welche" <prlw1@newn.cam.ac.uk> writes:

Is this a bug, or don't I understand coalesce()?

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..

Coalesce will return the first argument if it's not null.
You may be thinking about the arguments in reverse order?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

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

<20061212152219.GC290@quartz.itdept.newn.cam.ac.uk>, Patrick Welche
<prlw1@newn.cam.ac.uk> 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

On Tue, Dec 12, 2006 at 03:33:04PM +0000, Heikki Linnakangas wrote:

BTW: This type of questions really belong to pgsql-general or
pgsql-novice, this list is for discussing development of PostgreSQL itself.

^^^^^^

Indeed - I am truly feeling like a novice now...

Cheers,

Patrick