Re: sum multiple tables gives wrong answer?

In response to Michael Diener :
> 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;
>
> What is going on ??

It's a so called cross-join, every row form the first table crossed with
evvery row from the other table -> wrong result.

Simple example:

test=*# select * from t1;
id | flaeche
----+---------
1 | 10
2 | 20
(2 Zeilen)

Zeit: 0,229 ms
test=*# select * from t2;
id | flaeche
----+---------
1 | 100
2 | 200
(2 Zeilen)

Zeit: 0,182 ms
test=*# select sum(t1.flaeche), sum(t2.flaeche) from t1, t2;
sum | sum
-----+-----
60 | 600
(1 Zeile)

It's just this:

test=*# select * from t1, t2;
id | flaeche | id | flaeche
----+---------+----+---------
1 | 10 | 1 | 100
1 | 10 | 2 | 200
2 | 20 | 1 | 100
2 | 20 | 2 | 200
(4 Zeilen)

But you are looking for:

test=*# select (select sum(flaeche) from t1) as t1_flaeche, (select
sum(flaeche) from t2);
t1_flaeche | ?column?
------------+----------
30 | 300
(1 Zeile)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99