Обсуждение: sum multiple tables gives wrong answer?

----- Original Message -----

From: Michael Diener

To: pgsql-novice@postgresql.org

Cc: pgsql-general@postgresql.org

Sent: Wednesday, June 02, 2010 3:23 PM

Subject: [NOVICE] sum multiple tables gives wrong answer?

Hi,

 

I’m new to the list and have the following situation happening "PostgreSQL 8.4.1, compiled by Visual C++ build 1400, 32-bit" running windows XP sp3

 

I have an SQL problem that I thought was easy to do but gives me always the wrong answer.

 

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;

 

result:

greenHA

1.25358085

 

Wrong Answer with this query

select

 sum(green.flaeche)/10000 as "greens HA",

 sum (fairway.flaeche)/10000 as "fairway HA"

  from green, fairway;

 

result:

green HA                   fairway HA

48.8896531                 508.94143659

 

Fairway correct answer is  14.96886578 HA

Green correct answer is 1.25358085  HA

 

What is going on ??

 

Cheers

michael

 

Michael Diener

_________________________________________________________________

GOMOGI Mobile Geographics

LAKESIDE PARK B01

9020 KLAGENFURT

 

T: ++043 (0) 676 520 3600

E: m.diener@gomogi.com

W: www.gomogi.com

 

* Michael Diener (m.diener@gomogi.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