pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bill Cunningham <billc@ballydev.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: HAVING - clause
Date: Thu, 25 Apr 2002 11:51:03 -0700
Message-ID: <3CC85017.4020700@ballydev.com> (raw)
References: <200204251044.MAA16768@rodos>


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
>






view thread (3+ messages)

Message-ID: <3CC85017.4020700@ballydev.com>
Permalink:  ../3CC85017.4020700@ballydev.com/
Also on:    postgresql.org/message-id/3CC85017.4020700@ballydev.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: billc@ballydev.com
  Subject: Re: HAVING - clause
  In-Reply-To: <3CC85017.4020700@ballydev.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox