I'm using 8.1 from CVS head of about two days ago.

Extra columns seem to be on sum(plays.length), videos.path, videoid

create or replace view niceplays as SELECT count(*) AS plays,
summary("substring"(v.path, '[^/]+$'::text), 50) AS filename,
avg(p.length)::interval(0) AS avg, sum(p.length)::interval(0) AS sum,
(( SELECT now() - plays.playtimestamp
FROM plays
WHERE plays.videoid = v.videoid
ORDER BY plays.playid DESC
LIMIT 1))::interval(0) AS lastplay
FROM plays p
LEFT JOIN videos v USING (videoid)
WHERE p.length <> '00:00:00'::interval AND v.path ~~ '/home/allan/TransGaming_Drive/libp/%'::text
GROUP BY v.path, v.videoid
ORDER BY sum(p.length::interval(0)) DESC;

allan=# select * from niceplays;
plays | filename | avg | sum | lastplay
-------+----------+----------+----------+-----------------
13 | | 00:06:07 | 01:19:26 | 20 days 18:33:51
8 | | 00:07:18 | 00:58:23 | 17 days 23:49:38
8 | | 00:04:13 | 00:33:44 | 31 days 22:55:20
3 | | 00:10:56 | 00:32:47 | 17 days 00:04:18
7 | | 00:04:34 | 00:32:00 | 31 days 22:55:36
6 | | 00:05:04 | 00:30:22 | 17 days 23:46:32
[...]
(868 rows)

allan=# select * from niceplays order by sum desc limit 3;
plays | filename | avg | sum | lastplay | | |
-------+----------+----------+----------+------------------+----------+-+-----
13 | | 00:06:07 | 01:19:26 | 20 days 18:35:59 | 01:19:26 | | 43
8 | | 00:07:18 | 00:58:23 | 17 days 23:51:47 | 00:58:23 | | 1988
8 | | 00:04:13 | 00:33:44 | 31 days 22:57:29 | 00:33:43 | | 1961

Allan Wang


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly