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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Richard Broersma Sent: Mittwoch, 02. Juni 2010 17:39 To: m.diener@xxxxxxxxxx Cc: pgsql-novice@xxxxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx Subject: Re: [NOVICE] sum multiple tables gives wrong answer? On Wed, Jun 2, 2010 at 7:23 AM, Michael Diener <m.diener@xxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
