On 2 June 2010 15:23, Michael Diener <m.diener@gomogi.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