Postgres Pro
Downloads
Home   >   mailing lists

Thread: coalesce and aggregate functions

coalesce and aggregate functions

From
Patrick Welche
Date:
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?

Cheers,

Patrick
(PostgreSQL 8.2devel of 21st November 2006)

Re: coalesce and aggregate functions

From
Heikki Linnakangas
Date:
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

Re: coalesce and aggregate functions

From
Gregory Stark
Date:
"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

Re: coalesce and aggregate functions

From
"Kevin Grittner"
Date:
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
> have expected it only in the case where sum() returns null..
> 
> What am I missing?

Re: coalesce and aggregate functions

From
Patrick Welche
Date:
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