sum multiple tables gives wrong answer?

Howdy, Michael.

 

Your query is failing because you are doing the cartesian product of the tables with that query

Can't you do it on two different queries?

 

Say

select  sum(flaeche)/10000 as "greens HA"  from green;

and then

select  sum(flaeche)/10000 as "fairway HA"  from fairway;

?

 

Do you really need one single query?

 

If so, try this

 

select

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

 (sum (fairway.flaeche)/10000) / count(green.*) as "fairway HA"

  from green, fairway;

 

NB: This is untested code, it might contain syntactic/semantic bugs.

 

Best,

Oliveiros Cristina

 

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