I've got a sqlite table holding every played track in a row with played date/time Now I will count the plays of all artists, grouped by day and then find the artist with the max playcount per day. I used this Query
SELECT COUNT(ARTISTID) AS artistcount,
ARTIST AS artistname,
strftime('%Y-%m-%d', playtime) AS day_played
FROM playcount
GROUP BY artistname, day_played
to get this result
"93"|"The Skygreen Leopards"|"2010-06-16"
"2" |"Arcade Fire" |"2010-06-15"
"2" |"Dead Kennedys" |"2010-06-15"
"2" |"Wolf People" |"2010-06-15"
"3" |"16 Horsepower" |"2010-06-15"
"3" |"Alela Diane" |"2010-06-15"
"46"|"Motorama" |"2010-06-15"
"1" |"Ariel Pink's Haunted" |"2010-06-14"
I tried then to query this virtual table but I always get false results in artistname.
SELECT MAX(artistcount), artistname , day_played
FROM
(
SELECT COUNT(ARTISTID) AS artistcount,
ARTIST AS artistname,
strftime('%Y-%m-%d', playtime) AS day_played
FROM playcount
GROUP BY artistname
)
GROUP BY strftime('%Y-%m-%d',day_played)
result in this
"93"|"lilium" |"2010-06-16"
"46"|"Wolf People"|"2010-06-15"
"30"|"of Montreal"|"2010-06-14"
but the artist name is false. I think through the grouping by day, it just use the last artist, or so. I tested stuff like INNER JOIN or GROUP BY ... HAVING in trial and error, I read examples of similar issues but always get lost in columnnames and stuff (I am a bit burned out)
I hope someone can give me a hint. thanks m