Thread: I can not add up columns when using a left outer join. Any ideas as to why?

I can not add up columns when using a left outer join. Any ideas as to why?

From

"Robert Balzli Jr"

Date:

03 November 2006, 15:52:12

Hi All,

I want to add x and y. John and Joe do not have a “y” value, so the LEFT JOIN returns NULL. Likewise Jane does not have a value for “x” so the LEFT JOIN returns null for her. The “x” and “y” columns are correct, but I would expect the “total” column to have 24, 232, and 4. myview is a view that converts id numbers to the actual names. run, project and name come from myview. Both x and y come from the site table.

Here is my query:

SELECT project,name,

site.x,

site.y,

(site.x + site.y) AS "total"

FROM myview

LEFT JOIN site ON site.run_id = run

Here is what I get back…

project	name	x	y	total
6657	John	24
9065	Joe	232
21541	Jane		4

Can anyone tell me why the “total” column is empty?

Does the LEFT JOIN not apply to expressions?

Thanks for any help,

Robert Balzli

Re: I can not add up columns when using a left outer join. Any ideas as to why?

From

Richard Broersma Jr

Date:

03 November 2006, 16:23:48

> I want to add x and y. John and Joe do not have a "y" value, so the LEFT
> JOIN returns NULL. Likewise Jane does not have a value for "x" so the
> LEFT JOIN returns null for her. The "x" and "y" columns are correct, but
> I would expect the "total" column to have 24, 232, and 4.  myview is a
> view that converts id numbers to the actual names. run, project and name
> come from myview. Both x and y come from the site table.

> Can anyone tell me why the "total" column is empty?

becuase null + <pretty much anything> = null

check out COALESCE:
http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html#AEN12654

Regards,

Richard Broersm Jr.

Re: I can not add up columns when using a left outer join. Any ideas as to why?

From

Richard Broersma Jr

Date:

03 November 2006, 16:50:10

> COALESCE works great! Is there a way to default the LEFT JOIN to return
> zero and not NULL? Then we could use COALESCE on strings only. Most of
> our columns are integers not strings. This would make our queries
> simpler for 99% of the columns.
> Thanks,
> Robert Balzli
>
> PS: I will join the pgsql-general users group today...

No.  you will need to have:

select a, b, coalesce(a,0) + coalesce(b,0)
from your table;

Regards,

Richard Broersma Jr.

PS:
Also, don't forget to use reply all so that everyone one on the list can participate.

Re: I can not add up columns when using a left outer join. Any ideas as to why?

From

"Robert Balzli Jr"

Date:

05 November 2006, 00:08:32

Thanks Richard! All is working fine now...

> -----Original Message-----
> From: Richard Broersma Jr [mailto:rabroersma@yahoo.com]
> Sent: Friday, November 03, 2006 10:50 AM
> To: robert.balzli
> Cc: General PostgreSQL List
> Subject: RE: [GENERAL] I can not add up columns when using a left
outer
> join. Any ideas as to why?
>
> > COALESCE works great! Is there a way to default the LEFT JOIN to
return
> > zero and not NULL? Then we could use COALESCE on strings only. Most
of
> > our columns are integers not strings. This would make our queries
> > simpler for 99% of the columns.
> > Thanks,
> > Robert Balzli
> >
> > PS: I will join the pgsql-general users group today...
>
> No.  you will need to have:
>
> select a, b, coalesce(a,0) + coalesce(b,0)
> from your table;
>
> Regards,
>
> Richard Broersma Jr.
>
> PS:
> Also, don't forget to use reply all so that everyone one on the list
can
> participate.
>