Re: sum multiple tables gives wrong answer?

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Michael Diener <m(dot)diener(at)gomogi(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: sum multiple tables gives wrong answer?
Date: 2010-06-02 17:13:59
Message-ID: 20100602171359.GJ21875@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice

* Michael Diener (m(dot)diener(at)gomogi(dot)com) wrote:
> I have an SQL problem that I thought was easy to do but gives me always the
> wrong answer.

I think it's the right answer- the problem is that you're asking SQL a
different question than what you want the answer to.

> 2 Tables with a column called "flaeche" "double precision", in English
> "area" and I want to sum up the values for flaeche in each table to give me
> the total area for flaeche in each table.
>
> Correct answer comes with this sql
>
> select sum(flaeche)/10000 as "greens HA" from green;
>
> Wrong Answer with this query
>
> select
>
> sum(green.flaeche)/10000 as "greens HA",
>
> sum (fairway.flaeche)/10000 as "fairway HA"
>
> from green, fairway;
>
> What is going on ??

These are very different queries. Take out the sum()'s and see what you
get. What you'll find is that, because the join is unconstrained,
you're getting a cartesian product. Basically, each row in green is
being repeated for each row in fairway.

eg:

green has:
column1, column2
a, 1
b, 2
c, 3

fairway has:
column1, column2
x, 1
y, 2
z, 3

select * from green, fairway;

results:

a, 1, x, 1
a, 1, y, 2
a, 1, z, 3
b, 2, x, 1
b, 2, y, 2
b, 2, z, 3
c, 3, x, 1
c, 3, y, 2
c, 3, z, 3

What you really want to do is JOIN these two tables together, such as
in this:

select
green.column1 as green,
fairway.column1 as fairway,
column2
from
green
join fairway
using (column2)
;

results:
green,fairway
a, x, 1
b, y, 2
c, z, 3

Thanks,

Stephen

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2010-06-02 17:17:10 Re: libreadline and Debian 5 - not missing just badly named
Previous Message Merlin Moncure 2010-06-02 17:06:12 Re: PosttgreSQL on AIX

Browse pgsql-novice by date

  From Date Subject
Next Message Michael Diener 2010-06-02 18:45:56 Re: [NOVICE] sum multiple tables gives wrong answer?
Previous Message A. Kretschmer 2010-06-02 15:49:12 Re: sum multiple tables gives wrong answer?