Re: [NOVICE] 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(at)postgresql(dot)org
Cc: pgsql-general(at)postgresql(dot)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(dot)diener(at)gomogi(dot)com

W: www.gomogi.com