Re: sum multiple tables gives wrong answer?

Michael Diener wrote on 02.06.2010 16:23:
> 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.
>
> Wrong Answer with this query
>
> select
> sum(green.flaeche)/10000 as "greens HA",
> sum (fairway.flaeche)/10000 as "fairway HA"
> from green, fairway;

You are creating a cartesian product from the two tables.
You need to JOIN them properly, so that rows from green are properly related to the rows from the fairway table

As you didn't provide the table structure, we need to guess:

select sum(green.flaeche)/10000 as "greens HA",
        sum (fairway.flaeche)/10000 as "fairway HA"
FORM green
    JOIN fairway ON green.some_id = fairway.green_id

Thomas