Sweet SQL queries for Amarok

I was messing around writing some sweet SQL statements for amarok tonight. You can either run them using the MySQL console or using dcop (google ‘amarok dcop’). Here’s some examples:


# List artists and their average rating and number of ratings ordered by favorite artists first
SELECT a.name, avg(s.rating) avg, COUNT(s.rating) count FROM tags t, artist a, statistics s WHERE a.id=t.artist AND t.url=s.url GROUP BY a.name HAVING count > 10 ORDER BY avg DESC;



Comments

  1. jonas

    May 11, 2007 at 8:22 AM

    nice query. but if s.rating is 0 it means that you haven’t rated the title, thus such titles should be excluded from the query.

    <code>
    SELECT a.name, avg(s.rating) avg, COUNT(s.rating) count FROM tags t, artist a, statistics s WHERE a.id=t.artist AND s.rating!=0 AND t.url=s.url GROUP BY a.name HAVING count > 10 ORDER BY avg DESC;</code>