Music Stats

Introduction
I pulled my Amarok music database data to see what kind of things I could learn about my music collection. I imported a CSV into google sheets and from there it was quite easy. If I ever pull new data I can paste it in and it will automatically generate new graphs.

Totals

Artist

Album

Track Length

Rating

Genre

Year

SQL Query

SELECT
t.id,
t.album,
g.name AS genre,
y.name AS year,
0.001*t.length len_seconds,
a.name AS artist,
m.name AS album,
t.title AS trackname,
0.5 * r.rating AS rating,
u.rpath
FROM
artists a,
albums m,
tracks t,
statistics r,
years y,
genres g,
urls u
WHERE
a.id = t.artist AND m.id = t.album
AND r.id = t.id
AND y.id = t.year
AND g.id = t.genre
AND u.id = t.url;


Next Steps
I will repeat this once I have completed rating my collection. And I noticed there is some room for improvement.

1. Clean up the graphs and label them better
2. I had to delete all the 0 length tracks / silent tracks because they were throwing the results off (they are like 10% of my collection)
3. There are quite a few tracks that were 0, NULL or blank fields (e.g. genre, year, etc). My collection is pretty clean, could this be a poor SQL query?
4. Not sure if 100% of tracks were exported from database (ie 7600 tracks in this list by Amarok lists 11000)
5. Should have used ALBUMARTIST, not ARTIST