sum multiple tables gives wrong answer?

I replied just to him and I didnt notice, so Im just forwarding to group, in
case anyone need.

---- Or you may try Union, one for green other for fairway ----

On Wed, Jun 2, 2010 at 12:44 PM, Oliveiros
<oliveiros(dot)cristina(at)marktest(dot)pt>wrote:

> 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 <m(dot)diener(at)gomogi(dot)com>
> *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
>
>
>
>