Received: from bush.ballydev.com (mail.ballydev.com [216.82.146.240]) by postgresql.org (Postfix) with ESMTP id 9F673475852 for ; Thu, 25 Apr 2002 14:51:49 -0400 (EDT) Received: (from mail@localhost) by bush.ballydev.com (8.9.3/3.1) id LAA12093 for ; Thu, 25 Apr 2002 11:51:49 -0700 X-Authentication-Warning: bush.ballydev.com: mail set sender to using -f Received: from speedy.ballydev.com(10.2.200.27) by bush.ballydev.com via smap (V2.1/2.1+anti-relay+anti-spam) id xma012090; Thu, 25 Apr 02 11:51:31 -0700 Message-ID: <3CC85017.4020700@ballydev.com> Date: Thu, 25 Apr 2002 11:51:03 -0700 From: Bill Cunningham User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.9) Gecko/20020326 X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: pgsql-sql@postgresql.org Subject: Re: HAVING - clause References: <200204251044.MAA16768@rodos> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200204/315 X-Sequence-Number: 7579 I think you just want: select year as First_year,sample.type,avg(value),nuclide from sample,measure where sample.id = sampleid and year = ( select min(year) from sample ) or something like that. - Bill Christoph Haller wrote: >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 >> > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html >