pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christoph Haller <ch@rodos.fzk.de>
To: Morten.Sickel@nrpa.no
Cc: pgsql-sql@postgresql.org
Subject: Re: HAVING - clause
Date: Thu, 25 Apr 2002 12:44:51 METDST
Message-ID: <200204251044.MAA16768@rodos> (raw)
In-Reply-To: <54DE9A561AD20C4D9FF88B116965420E02A037@postix.nrpa.no>

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
> 




view thread (3+ messages)  latest in thread

Message-ID: <200204251044.MAA16768@rodos>
Permalink:  ../200204251044.MAA16768@rodos/
Also on:    postgresql.org/message-id/200204251044.MAA16768@rodos

 · 

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: ch@rodos.fzk.de, Morten.Sickel@nrpa.no
  Subject: Re: HAVING - clause
  In-Reply-To: <200204251044.MAA16768@rodos>

* 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