Count vs Sum to find The number of times the track was purchased in 2020

Screen Link: Learn data science with Python and R projects

My Code:

``````select t1.track_id, t1.name,
sum(i_l.quantity) as no_of_purchases
from track as t1
left join invoice_line as i_l
on t1.track_id = i_l.track_id
left join invoice as i
on i_l.invoice_id = i.invoice_id and substring(i.invoice_date,1,4) = '2020'
group by t1.track_id, t1.name
``````

What I expected to happen:

I thought the `sum(i_l.quantity)` would find the total number of purchases for a song in 2020, but the answer shows `COUNT(i.invoice_id)`. Am I just misinterpreting the question? The correct solution appears to reflect how many purchase orders included a particular song in 2020, whereas my solution (I think) reflects the actual quantity of how many times each song was purchased in 2020.

What actually happened:

Column `no_of_purchases` has `3302` incorrect values. For example, its `1st` value should be `1` , not `8` .

Hint: Remove the `GROUP BY` clause, select the columns `t1.track_id, t1.name, i_l.quantity, i_l.invoice_id, i.invoice_id` and filter for `track_id = 1`, to make it concrete. Then take a look at the results and run the query in your head with the result in mind.