How does this calculate the number of tracks purchased, when the * wildcard selects all of the columns? I am also confused why we are joining the subquery (ta) with the invoice line table, and how this helps us. I am having a hard time conceptualizing this. Thanks!
COUNT(*) simply counts all the rows in the table that results from that query. In this particular case, since you are grouping by column 1 (denoted by the GROUP BY 1 line), the COUNT(*) line will count the number of rows (i.e. tracks) that each unique album has!
From the code it also looks like the ta table that is being joined is used to provide the title and name columns. I believe this answers your other question.
On a side-note, do keep in mind that generally it’s far better to copy-paste your code using the appropriate code formatting (all you have to do is add 3 back-ticks before and after your code), instead of using screenshots.
Hi thanks so much for your answer! So I have a couple of follow up questions:
In the subquery, why do we need to select the column “t.track_id”? Is it because that is what we are using to join the subquery to the invoice_line table?
In the answer, it actually groups by the first and second columns (GROUP BY 1, 2) and I don’t understand the reason for that. Shouldn’t just grouping by the first, “album” column be enough, since we are looking for the top album sales? We aren’t looking for the top sales according to artist, so I don’t understand why we group both columns.
Here is the code again pasted below. Thanks so much! (PS. I am not sure how you want me to format it for you. But I copy and pasted it so hopefully it is readable.
FROM invoice_line il
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
ON ta.track_id = il.track_id
GROUP BY 1, 2
ORDER BY 3 DESC LIMIT 5;