Wednesday, July 29, 2009

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

4 comments:

PaulWay said...

Why are you using aggregate functions on fields in the group by clause? You should be able to do:

select artist, album, avg(length)
from fs
group by artist, album;

monkeyiq said...

While SQLite allows you to leave off the aggregate functions, other RDBMS (the majority) do not. I try to make my SQL as portable as possible.

Peter Becker said...

I didn't know you were into the Floyd :-)

monkeyiq said...

I just assume that most folks are, its an "opt out" system ;-p