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

On 2 June 2010 15:23, Michael Diener <m(dot)diener(at)gomogi(dot)com> wrote:
> 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
>
> _________________________________________________________________

Could it be because you're effectively using a cartesian join?

Can't you do them separately? Like:

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

Or if you must have both in the same result:

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

Regards

Thom