Sweet SQL queries for Amarok
Posted by hank, Fri May 11 04:22:00 UTC 2007
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;
Blog Posts
September 22, 2007 @ 02:16 PM
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.
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;