Re: HAVING - clause - Mailing list pgsql-sql
From | Bill Cunningham |
---|---|
Subject | Re: HAVING - clause |
Date | |
Msg-id | 3CC85017.4020700@ballydev.com Whole thread Raw |
In response to | Re: HAVING - clause (Christoph Haller <ch@rodos.fzk.de>) |
List | pgsql-sql |
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 >