SQL - Count grouped entries and then get the max values grouped by date

Question

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

Answer 1

That's exactly what's happening. I wouldn't even expect this query to run - since you've got "artistname" in the SELECT clause but not in the GROUP BY, I'd expect the SQL engine you're working with to refuse to execute the query.

To resolve it, just add "artistname" to your GROUP BY in the outer query:

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 artistname, strftime('%Y-%m-%d',day_played)

Answer 2

I've found a way after reading this: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ But it feels stupid and maybe someone could give a hint how to optimize this.

I've created a sqlite view called *max_play_by_artist* which output all playcounts grouped by day_played

CREATE VIEW "max_play_by_artist"
AS
    SELECT COUNT(artistid) AS artistcount,
    artist AS artistname,
    strftime('%Y-%m-%d', playtime) AS day_played,
    artistid as id
    FROM playcount
    GROUP BY artistid,day_played

then I query the view with the following statement

SELECT b.artistcount, b.artistname, b.day_played, b.id
FROM 
(
    SELECT day_played, MAX(artistcount) as max_count
    FROM max_play_by_artist
    GROUP BY day_played
) AS a
INNER JOIN max_play_by_artist AS b
ON b.day_played = a.day_played
AND b.artistcount = a.max_count

this gives me the desired result.