You are here

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

Total Artists 797
Total Albums 762
Total Tracks 6960
Total Length 23.39 days
Min Length 5s
Max Length 60.83m
Avg Length 4.57m
Total Genres 69
Min Year 0
Max Year 2015
Avg Year 1960
Avg Rating 3.67

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
Tags: 

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <table> <tr> <td> <ul> <ol> <li> <dl> <dt> <pre> <dd> <img> <sub> <sup>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
2 + 6 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.