Re: interval questions

At 10:21 PM -0500 6/1/2000, Ed Loehr wrote:
>Michael Blakeley wrote:
>>
>>  CREATE TABLE EVENTS( stamp date, id varchar(16), event varchar(128) );
>>
>>  I'm trying to find the average age of the records. I've gotten as far as:
>>          SELECT DISTINCT ON(id) age(stamp) FROM EVENTS;
>>
>>  Now, I need the DISTINCT ON(id), but that means I can't simply
>>avg() the age:
>>          ERROR:  Attribute events.id must be GROUPed or used in an
>>  aggregate function
>>
>
>Interesting problem.  Would this do it?
>
>    select into temp_age id, sum(age(stamp)) as age_sum, count(id)
>    from EVENTS group by id;
>
>followed by
>
>    select avg(age_sum/count) from temp_age;

I oversimplified - I left out the outer join, which I was performing
in the wrong (non-unique id) direction. I wanted to query for the age
of ids that have had events (recently, but I'll omit that part). The
following is a little closer to what I was trying to do:
    CREATE TABLE IDS (id varchar(16) primary key, created date);
    SELECT DISTINCT ON(id) avg(age(IDS.created))) FROM EVENTS WHERE id=IDS.id;

Reversing the join gives me unique ids, and allowed me to leave out
the DISTINCT ON clause. So avg() now works, and gives me the single
number I was after. Like:
    SELECT AVG(AGE(created))) FROM IDS WHERE id=EVENTS.id;

Thanks for the help - it wasn't until I explained the problem
properly that I figured it out :-).

-- Mike