Thread: sum multiple tables gives wrong answer?
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
_________________________________________________________________
GOMOGI Mobile Geographics
LAKESIDE PARK B01
9020 KLAGENFURT
T: ++043 (0) 676 520 3600
E: m.diener@gomogi.com
W: www.gomogi.com
On 2 June 2010 15:23, Michael Diener <m.diener@gomogi.com> 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
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
On Wed, Jun 2, 2010 at 7:23 AM, Michael Diener <m.diener@gomogi.com> wrote: > select sum(flaeche)/10000 as "greens HA" from green; > result: > Wrong Answer with this query > > select > sum(green.flaeche)/10000 as "greens HA", > sum (fairway.flaeche)/10000 as "fairway HA" > from green, fairway; It isn't easy to see but you are effectively joining green to fairway using a cross project which product a Cartesian product. you probably wanted this query: SELECT (select sum(flaeche)/10000 from green) AS "greens HA", (select sum(flaeche)/10000 from fairway) AS "fairway HA"; However, from what you've shown. I would wager that your database is in need of some normalization. For example you could put both greens and fair way into a single table like: CREATE TABLE Lawns AS SELECT flaech, "green"::VARCHAR AS lawntype FROM green UNION ALL SELECT flaech, "fairway"::VARCHAR AS lawntype FROM fairway; Then you'd execute the following query: SELECT lawntype, sum(flaech)/10000 AS "HA" FROM Lawns GROUP BY lawntype; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
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
select
(sum(green.flaeche)/10000) / count(fairway.*) as "greens HA",
(sum (fairway.flaeche)/10000) / count(green.*) as "fairway HA"
from green, fairway;
----- Original Message -----From: Michael DienerSent: Wednesday, June 02, 2010 3:23 PMSubject: [NOVICE] sum multiple tables gives wrong answer?Hi,
Im 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
_________________________________________________________________
GOMOGI Mobile Geographics
LAKESIDE PARK B01
9020 KLAGENFURT
T: ++043 (0) 676 520 3600
E: m.diener@gomogi.com
W: www.gomogi.com
I just want to say thanks to all the great responses it is now working like a charm!! I knew I was missing some tid bit of DB SQL knowledge. Thanks again! Cheers michael -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard Broersma Sent: Mittwoch, 02. Juni 2010 17:39 To: m.diener@gomogi.com Cc: pgsql-novice@postgresql.org; pgsql-general@postgresql.org Subject: Re: [GENERAL] [NOVICE] sum multiple tables gives wrong answer? On Wed, Jun 2, 2010 at 7:23 AM, Michael Diener <m.diener@gomogi.com> wrote: > select sum(flaeche)/10000 as "greens HA" from green; > result: > Wrong Answer with this query > > select > sum(green.flaeche)/10000 as "greens HA", > sum (fairway.flaeche)/10000 as "fairway HA" > from green, fairway; It isn't easy to see but you are effectively joining green to fairway using a cross project which product a Cartesian product. you probably wanted this query: SELECT (select sum(flaeche)/10000 from green) AS "greens HA", (select sum(flaeche)/10000 from fairway) AS "fairway HA"; However, from what you've shown. I would wager that your database is in need of some normalization. For example you could put both greens and fair way into a single table like: CREATE TABLE Lawns AS SELECT flaech, "green"::VARCHAR AS lawntype FROM green UNION ALL SELECT flaech, "fairway"::VARCHAR AS lawntype FROM fairway; Then you'd execute the following query: SELECT lawntype, sum(flaech)/10000 AS "HA" FROM Lawns GROUP BY lawntype; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
---- Or you may try Union, one for green other for fairway ----
Howdy, Michael.Your query is failing because you are doing the cartesian product of the tables with that queryCan't you do it on two different queries?Sayselect sum(flaeche)/10000 as "greens HA" from green;and thenselect sum(flaeche)/10000 as "fairway HA" from fairway;?Do you really need one single query?If so, try thisselect
(sum(green.flaeche)/10000) / count(fairway.*) as "greens HA",
(sum (fairway.flaeche)/10000) / count(green.*) as "fairway HA"
from green, fairway;
NB: This is untested code, it might contain syntactic/semantic bugs.Best,Oliveiros Cristina----- Original Message -----From: Michael DienerSent: Wednesday, June 02, 2010 3:23 PMSubject: [NOVICE] sum multiple tables gives wrong answer?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
_________________________________________________________________
GOMOGI Mobile Geographics