From: | Morten Sickel <Morten(dot)Sickel(at)nrpa(dot)no> |
---|---|
To: | "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org> |
Subject: | HAVING - clause |
Date: | 2002-04-25 09:32:40 |
Message-ID: | 54DE9A561AD20C4D9FF88B116965420E02A037@postix.nrpa.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I have a data base for storing information on radioactive contamination in
various samples. I want to dig out for a set of the samples the first year
for which I have information for the sample types and a radio nuclides and
the average activity for that year. The relevant part of my data base are
two tables, sample and measure, which a bit simplified can be described as
create table sample(
id serial primary key,
type char()
year integer
)
create table measure(
id serial primary key,
sample id integer references sample(id),
nuclide char(),
value float
)
I tried to do a
select year as First_year,sample.type,avg(value),nuclide
from sample,measure
where sample.id = sampleid
group by sample.type,nuclide
having year=min(year)
But it told me that year has to be grouped to be used in this way, so I
grouped, but then I got averages for each year, and the same result if I
also put a min(year) in the select part of the statement...
If I do a
select min(year) as First_year,sample.type,nuclide
from sample,measure
where sample.id = sampleid
group by sample.type,nuclide
I get the first year for each sample type and nuclide, but then I need the
average for each of those first years... I thought I should use having for
that, but I have obiously overlooked or misunderstood something..
Any clues?
Morten
--
Morten Sickel
Norwegian Radiation Protection Authority
From | Date | Subject | |
---|---|---|---|
Next Message | Helge Kreutzmann | 2002-04-25 10:22:53 | Proper quoting of \e ? |
Previous Message | Rajesh Kumar Mallah | 2002-04-25 07:10:12 | Re: Database Server in Recovery mode! |