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
