Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux