Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by postgresql.org (Postfix) with SMTP id 4B56E474E71 for ; Thu, 25 Apr 2002 06:44:51 -0400 (EDT) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Thu Apr 25 12:44:52 2002 +0200 Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id MAA02379; Thu, 25 Apr 2002 12:44:52 +0200 (METDST) From: Christoph Haller Message-Id: <200204251044.MAA16768@rodos> Subject: Re: HAVING - clause To: Morten.Sickel@nrpa.no (Morten Sickel) Date: Thu, 25 Apr 2002 12:44:51 METDST Cc: pgsql-sql@postgresql.org In-Reply-To: <54DE9A561AD20C4D9FF88B116965420E02A037@postix.nrpa.no>; from "Morten Sickel" at Apr 25, 102 11:34 am X-Mailer: Elm [revision: 212.4] X-Archive-Number: 200204/299 X-Sequence-Number: 7563 I'm not sure if this is what you intend to retrieve, but try select distinct on ( sample.type,nuclide ) year as First_year,sample.type,avg(value),nuclide from sample,measure where sample.id = sampleid group by sample.type,nuclide,year order by sample.type,nuclide,year asc ; Regards, Christoph PS If this works, a short reply would be nice. > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >