Amarok/Archives/Proposals/Statistics

It would be nice to see some (fairly) random statistics in amaroK, based on one's music collection, time listening, et cetera

For example, statistics of how much of a listener's music is of a certain genre (for our purposes, we'll use Rock) Have the statistics code gather up all the genre information in the database, which, for many songs, simply will not show up, but this IS editable by the user, so they can class their music as they see fit. Tally the total number of the genre then it's simple math Number In Genre/Total songs

The same can be done with percentage of artists, but it would be Number of songs by Artist/Total songs

And it would also be nice to do the above with the TIME of the songs For example Total time of songs from album/Time of all songs Total time of songs by artist/Time of all songs Total time of songs in genre/Time of all song

Another feature that would be nice (Number of songs in genre A)/(nubmer of songs in Genre B) Example output User listens to Genre A twice as much as Genre B The same could also be done for artists and albums.

SQL Query Collection

These collection may be used in your IRC client, PHP scripts, on your command line or in other programs. Usage via command line:

dcop amarok collection query "[your query]"

Quantities

• Artist count

SELECT COUNT(*) from artist

• Song count

SELECT COUNT(title) from tags

• Album Count

SELECT COUNT(*) from album

• Played Songs

SELECT count(playcounter) from statistics

Statistics

• Last song played on ...

SELECT DATE_FORMAT(FROM_UNIXTIME(MAX(accessdate)), '%d.%m.%Y') from statistics

• First song played on ...

SELECT DATE_FORMAT(FROM_UNIXTIME(MIN(accessdate)), '%d.%m.%Y') from statistics

• Average bitrate

SELECT ROUND(AVG(bitrate),0) from tags

• Average play count

SELECT ROUND(AVG(playcounter),1) from statistics

• Total play count

SELECT SUM(playcounter) from statistics

• Songs skipped after an average of X percent

SELECT ROUND(AVG(percentage),1) from statistics

• Total Time in minutes (s/60=m)

SELECT ROUND(SUM(length)/60,2) from tags

• total time in hours (m / [60*60=3600] = h)

SELECT ROUND(SUM(length)/3600,2) from tags

• total time in days (h / [3600*24=86400] = d)

SELECT ROUND(SUM(length)/86400,2) from tags

• total time in years (d / [86400*365=31536000] = y)

SELECT ROUND(SUM(length)/31536000,2) from tags