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

On Wed, Jun 2, 2010 at 7:23 AM, Michael Diener <m(dot)diener(at)gomogi(dot)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