select...group by artist

So I finally updated my Amarok mounting to work with 2.x. I'm still having a few issues with the 2.x of 'rok but that's another story.

One of the cool things about being able to mount things with libferris is that everything above ferris can also see them, XQuery, SQLite etc. For example, the amarok://playlist URL shows, as you would imagine, the playlist as it currently appears in the running amarok application.

The following little SQLite is fairly self explanatory:
.load libferrissqlitevtable.so
create virtual table fs using libferris(
'amarok://playlist',
'',
name text,
title text,
size int,
length int,
playtime text,
link-target text,
ferris-delegate-url text
);

select name, playtime, title from fs
order by name desc;

select avg(length) from fs;


And for a particular album, the playlist and average track time.

9|352|Absolutely Curtains
8|246|Stay
7|255|Free Four
6|274|Childhood's End
5|260|Mudmen
4|309|Wots ... Uh The Deal
3|187|The Gold It's In The ...
2|209|Burning Bridges
1|147|When You're In
0|185|Obscured By Clouds

242.4

Of course, a litttle group by artist, album... would make the SQL more generic:
select avg(length),max(artist),max(album)
from fs
group by artist, album;

285.777777777778|Pink Floyd|Dark Side of the Moon
242.4|Pink Floyd|Obscured By Clouds