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
