Group by 2 columns?

Q&A DQ Courses
,
#1

Screen Link: https://app.dataquest.io/m/189/intermediate-joins-in-sql/4/combining-multiple-joins-with-subqueries

SELECT
    ta.album_title AS album,
    ta.artist_name AS artist,
    COUNT(*) AS tracks_purchased
FROM invoice_line AS il
INNER JOIN (
            SELECT
                t.track_id,
                al.title AS album_title,
                ar.name AS artist_name
            FROM track AS t
            INNER JOIN album AS al ON al.album_id = t.album_id
            INNER JOIN artist AS ar ON ar.artist_id = al.artist_id
           ) AS ta
           ON ta.track_id = il.track_id
GROUP BY 1, 2
ORDER BY 3 DESC LIMIT 5;

Can someone explain to me why it is necessary to GROUP BY both album and artist here, rather than just by album?

3 Likes
#2

You are right.

I grouped by album when I solved and I got the pass.

SELECT
    ta.title album,
    ta.artist_name artist,
    COUNT(*) tracks_purchased
    FROM invoice_line il
    INNER JOIN (
        SELECT
            t.track_id,
            ar.name artist_name,
            al.title
            FROM track t
            INNER JOIN album al ON al.album_id = t.album_id
            INNER JOIN artist ar ON ar.artist_id = al.artist_id
            ) ta
            ON ta.track_id = il.track_id
     GROUP BY 1
     ORDER BY 3 DESC
     LIMIT 5;
3 Likes
#3

Hiii @bc330,

I guess it would make difference if there are two artists associated with a single album. Other than that, I think it’s okay to group by just album.

#4

Hi @bc330,
I had the same question and found this answer on a future exercise helpful:


Happy learning!

4 Likes