SQL - Count grouped entries and then get the max values grouped by date
- by Marcus
hello,
I am out of any logic how to write the right sql statment.
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
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
