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

["Oliveiros" <oliveiros.cristina@xxxxxxxxxxx>]

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

Cc: pgsql-general@xxxxxxxxxxxxxx

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

W: www.gomogi.com