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-nov...@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.die...@gomogi.com
W: www.gomogi.com
