From: | Alfred Perlstein <bright(at)wintelcom(dot)net> |
---|---|
To: | Michael Blakeley <mike(at)blakeley(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: interval questions |
Date: | 2000-06-02 02:48:28 |
Message-ID: | 20000601194828.E17973@fw.wintelcom.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
* Michael Blakeley <mike(at)blakeley(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Smith | 2000-06-02 03:08:48 | Re: ALTERING A TABLE |
Previous Message | Tom Lane | 2000-06-02 02:42:40 | Re: interval questions |