From: | Olaf Marc Zanger <olaf(dot)zanger(at)soli-con(dot)com> |
---|---|
To: | PgSQL-SQL <pgsql-sql(at)postgresql(dot)org> |
Subject: | a tricky one |
Date: | 2001-02-24 15:55:40 |
Message-ID: | 01022416554000.01281@sino |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(dot)zanger(at)soli-con(dot)com, http://www.soli-con.com
From | Date | Subject | |
---|---|---|---|
Next Message | Tod McQuillin | 2001-02-24 15:59:12 | Re: syntax prob |
Previous Message | Richard Huxton | 2001-02-24 11:23:37 | Re: Estimation of SQL statements |