Received: from relay01.cablecom.net (relay01.cablecom.net [62.2.33.101]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1OFtfx78381 for ; Sat, 24 Feb 2001 10:55:42 -0500 (EST) (envelope-from olaf.zanger@soli-con.com) Received: from mail.swissonline.ch (mail.swissonline.ch [62.2.32.83]) by relay01.cablecom.net (8.11.1/8.11.0/SOL/MXRELAY-1.03) with ESMTP id f1OFtWm83066 for ; Sat, 24 Feb 2001 16:55:32 +0100 (CET) Received: from sino (dclient164-21.hispeed.ch [62.2.164.21]) by mail.swissonline.ch (8.11.1/8.11.0/MSOL-2.17) with SMTP id f1OFtWj27699 for ; Sat, 24 Feb 2001 16:55:32 +0100 (MET) Content-Type: text/plain; charset="iso-8859-1" From: Olaf Marc Zanger Reply-To: olaf.zanger@soli-con.com Organization: soli-con Engineering Zanger To: PgSQL-SQL Subject: a tricky one Date: Sat, 24 Feb 2001 16:55:40 +0100 X-Mailer: KMail [version 1.2] MIME-Version: 1.0 Message-Id: <01022416554000.01281@sino> Content-Transfer-Encoding: 8bit X-Archive-Number: 200102/383 X-Sequence-Number: 1588 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