* Michael Blakeley <mike@blakeley.com> [000601 19:09] wrote:
> I hope someone on the list can suggest a solution for me - given a table like
>
> 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
>
> Can anyone suggest a solution? I could do the averaging myself,
> except that the output is non-trivial to parse:
> 7 mons 6 10:29
> 2 mons 30 07:43:38
> 3 mons 4 09:50:56
> (To be accurate, my code has to get the days in each month right,
> etc., and it feels like I'm reinventing the wheel there.)
>
> Thanks in advance for any suggestions.
Does this work for you:
SELECT DISTINCT ON(id) avg(age(stamp)) FROM EVENTS group by id;
?
-Alfred
