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