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

Michael Blakeley <mike@blakeley.com> writes:

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

You don't say *why* you need DISTINCT ON, or exactly what output you
are hoping to get (presumably not a straight average over all the table
entries) ... but perhaps something like
SELECT id, avg(age(stamp)) FROM events GROUP BY id;
is what you need?

regards, tom lane

* 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

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;

Regards,
Ed Loehr