Skip site navigation (1) Skip section navigation (2)

Re: sum multiple tables gives wrong answer?

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: sum multiple tables gives wrong answer?
Date: 2010-06-02 15:49:12
Message-ID: 20100602154912.GA20816@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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

In response to

pgsql-novice by date

Next:From: Stephen FrostDate: 2010-06-02 17:13:59
Subject: Re: sum multiple tables gives wrong answer?
Previous:From: OliveirosDate: 2010-06-02 15:44:09
Subject: Re: [NOVICE] sum multiple tables gives wrong answer?

pgsql-general by date

Next:From: Peter GeogheganDate: 2010-06-02 15:49:57
Subject: Re: server-side extension in c++
Previous:From: J. BaggDate: 2010-06-02 15:48:52
Subject: libreadline and Debian 5 - not missing just badly named

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group