most popular artist over the last week

[tim]

hi, is this possible?

i presume this could be done using a sql query but i don't really know where to start or how to add up the total for each artist?

so, could someone please please supply me with the sql needed for this because i think i can adapt it to display in a nice table etc just by adapting the stats script that comes with it.

i think it could be quite an interesting stat and sorry for having to ask you guys. being able to do this would actually pretty much replicate what audioscrobbler shows at the moment only all locally instead. thanks loads for any assistance though.

[onkel_enno]

This Query would result all Artist of the last week:

Code: Select all

SELECT Artists.Artist
FROM Artists INNER JOIN (Songs INNER JOIN Played ON Songs.ID = Played.IdSong) ON Artists.ID = Songs.IDArtist
WHERE Played.PlayDate>=(Date()-7)
GROUP BY Artists.Artist;

but how do you define "most popular"?

[tim]

sorry, the popularity would be based on the total play count for that artist, should've made that clear

thanks a lot for that though

EDIT:
after playing around with access for a while i got:

SELECT Artists.Artist, Count(Played.PlayDate) AS CountOfPlayDate
FROM (Artists INNER JOIN Songs ON Artists.ID = Songs.IDArtist) INNER JOIN Played ON Songs.ID = Played.IdSong
WHERE (((Played.PlayDate)>=(Date()-7)))
GROUP BY Artists.Artist;

which seems to work correctly. thanks for the help

[Risser]

So, now that you have the SQL, what do you do with it? Can you populate a Playlist with it? If so, I'd like to know how, because I've got some SQL that generates some weighted random tracks and I'd like to know how to populate a playlist.

Right now, I populate a regular list node.
Peter

[tim]

nah, i have no idea how to do that, it would be interesting though

i just plugged the sql into the stats script that comes with mediamonkey so that three extra tables are displayed showing the most popular artists, albums and songs over the last week (or any other specified amount of time).