Search Postgresql Archives

Re: [NOVICE] sum multiple tables gives wrong answer?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux