pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Olaf Marc Zanger <olaf.zanger@soli-con.com>
To: PgSQL-SQL <pgsql-sql@postgresql.org>
Subject: a tricky one
Date: Sat, 24 Feb 2001 16:55:40 +0100
Message-ID: <01022416554000.01281@sino> (raw)

hi there, 

something brainboggling :-)

three tables:
fac with:
id                   | integer       | not null default 
nextval('fac_id_seq'::text)                                                   
 
pre with:
  id          | integer | not null default nextval('pre_id_seq'::text)
 fac_id      | integer |
 date        | date    |              
 production       | float8    |              

prd with:
 id                | integer | not null default nextval('prd_id_seq'::text)
 date              | date    |
 fac_id            | integer |
 prediction           | float8 |

the last two have constraints as foreign keys  "fac_id int4 references fac 
(id)"

pre has only one row per month
prd has one value per day

if i do a 

select

count(fac.id)
 as fac_id,
sum(prd.production)
 as prd_production,
sum(pre.prediction)
 as pre_prediction

from
fac,
pre,
prd

where
date_part('year',timestamp(prd.date))=date_part('year',timestamp(pre.date))
and
date_part('month',timestamp(prd.date))=date_part('month',timestamp(pre.date))
and
pre.fac_id=fac.id
and
prd.fac_id=fac.id

group by
fac.id

what i acctually want is a result that shows the summed production of a year 
and the summed prediction of a year at the same time 


i got values of 365 for fac_id before i had the constraints :-), which was 
perfectly well.

now it shows 
372 (like 31x12) if i keep it as is
4392 (like 365x12) if i ommit the month-where-clause.

what's wrong

can anybody help, this goes over my know-how :-)
happy weekend

and thanks for the great help lately from the list

olaf

-- 
soli-con Engineering Zanger, Dipl.-Ing. (FH) Olaf Marc Zanger
Lorrainestrasse 23, 3013 Bern / Switzerland
fon:+41-31-332 9782, mob:+41-76-572 9782
mailto:olaf.zanger@soli-con.com, http://www.soli-con.com



Message-ID: <01022416554000.01281@sino>
Permalink:  ../01022416554000.01281@sino/
Also on:    postgresql.org/message-id/01022416554000.01281@sino

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: olaf.zanger@soli-con.com
  Subject: Re: a tricky one
  In-Reply-To: <01022416554000.01281@sino>
  References: <01022416554000.01281@sino>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox