Skip site navigation (1) Skip section navigation (2)

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

From: "Oliveiros" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: <m(dot)diener(at)gomogi(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: [NOVICE] sum multiple tables gives wrong answer?
Date: 2010-06-02 15:44:09
Message-ID: 3AEE0ADFAD174B90BD470E6D812D0333@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
Howdy, Michael.

Your query is failing because you are doing the cartesian product of the tables with that query
Can't you do it on two different queries?

Say 
select  sum(flaeche)/10000 as "greens HA"  from green;
and then
select  sum(flaeche)/10000 as "fairway HA"  from fairway;
?

Do you really need one single query?

If so, try this

select 

 (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 Diener 
  To: pgsql-novice(at)postgresql(dot)org 
  Cc: pgsql-general(at)postgresql(dot)org 
  Sent: Wednesday, June 02, 2010 3:23 PM
  Subject: [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

  LAKESIDE PARK B01

  9020 KLAGENFURT

   

  T: ++043 (0) 676 520 3600

  E: m(dot)diener(at)gomogi(dot)com

  W: www.gomogi.com

   

In response to

Responses

pgsql-novice by date

Next:From: A. KretschmerDate: 2010-06-02 15:49:12
Subject: Re: sum multiple tables gives wrong answer?
Previous:From: Richard BroersmaDate: 2010-06-02 15:38:36
Subject: Re: [NOVICE] sum multiple tables gives wrong answer?

pgsql-general by date

Next:From: J. BaggDate: 2010-06-02 15:48:52
Subject: libreadline and Debian 5 - not missing just badly named
Previous:From: Tim LandscheidtDate: 2010-06-02 15:42:26
Subject: Re: Is it possible to make the order of output the same as the order of input parameters?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group