View source for 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:
{{Output|<nowiki>dcop amarok collection query "[your query]"</nowiki>}}

== Quantities ==
* Artist count
{{Output|<nowiki>SELECT COUNT(*) from artist</nowiki>}}
* Song count
{{Output|<nowiki>SELECT COUNT(title) from tags</nowiki>}}
* Album Count
{{Output|<nowiki>SELECT COUNT(*) from album</nowiki>}}
* Played Songs
{{Output|<nowiki>SELECT count(playcounter) from statistics</nowiki>}}

== Statistics ==
* Last song played on ...
{{Output|<nowiki>SELECT DATE_FORMAT(FROM_UNIXTIME(MAX(accessdate)), '%d.%m.%Y') from statistics</nowiki>}}
* First song played on ...
{{Output|<nowiki>SELECT DATE_FORMAT(FROM_UNIXTIME(MIN(accessdate)), '%d.%m.%Y') from statistics</nowiki>}}
* Average bitrate 
{{Output|<nowiki>SELECT ROUND(AVG(bitrate),0) from tags</nowiki>}}
* Average play count
{{Output|<nowiki>SELECT ROUND(AVG(playcounter),1) from statistics</nowiki>}}
* Total play count
{{Output|<nowiki>SELECT SUM(playcounter) from statistics</nowiki>}}
* Songs skipped after an average of X percent
{{Output|<nowiki>SELECT ROUND(AVG(percentage),1) from statistics</nowiki>}}
* Total Time in minutes (s/60=m)
{{Output|<nowiki>SELECT ROUND(SUM(length)/60,2) from tags</nowiki>}}
* total time in hours (m / [60*60=3600] = h)
{{Output|<nowiki>SELECT ROUND(SUM(length)/3600,2) from tags</nowiki>}}
* total time in days (h / [3600*24=86400] = d)
{{Output|<nowiki>SELECT ROUND(SUM(length)/86400,2) from tags</nowiki>}}
* total time in years (d / [86400*365=31536000] = y)
{{Output|<nowiki>SELECT ROUND(SUM(length)/31536000,2) from tags</nowiki>}}