SELECT T.track_id, T.name, COUNT(*) AS no_of_purchases
FROM invoice_line AS il JOIN track AS t
ON t.track_id=il.track_id
JOIN invoice AS i
ON il.invoice_id=i.invoice_id
WHERE i.invoice_date like '%2020%'
What I expected to happen:
Provide a list of all songs in track and how many times they were purchased in 2020
What actually happened:
[1 rows x 3 columns]
track_idnameno_of_purchases149Black Sabbath1150
The solution is below:
SELECT t.track_id, t.name,
COUNT(i.invoice_id) AS no_of_purchases
FROM track AS t
LEFT JOIN invoice_line AS il
ON t.track_id = il.track_id
LEFT JOIN invoice AS i
ON il.invoice_id = i.invoice_id AND invoice_date LIKE ‘2020%’
GROUP BY t.track_id, t.name;
I didn’t get why count(*) would not be used in this situation, why left join was used. I somewhat get why group by was used but I thinking it would be Group by invoice_id
Here’s a version of your query focused on just one track:
SELECT t.track_id, t.name, i.invoice_id, i.invoice_date
FROM track AS t
LEFT JOIN invoice_line AS il
ON t.track_id = il.track_id
LEFT JOIN invoice AS i
ON il.invoice_id = i.invoice_id AND invoice_date LIKE '2020%'
WHERE t.track_id = 1;
track_id
name
invoice_id
invoice_date
1
For Those About To Rock (We Salute You)
1
For Those About To Rock (We Salute You)
1
For Those About To Rock (We Salute You)
1
For Those About To Rock (We Salute You)
1
For Those About To Rock (We Salute You)
1
For Those About To Rock (We Salute You)
1
For Those About To Rock (We Salute You)
1
For Those About To Rock (We Salute You)
496
2020-04-01 00:00:00
The empty cells tell you that the invoices to which that track belongs are not from 2020. When you do COUNT(*), you’re counting all the rows when you just wanted to count the one on the bottom because it’s the only one that matches the criteria.
hi Bruno thank you so much for the explanation. I had one question though what if instead of Left Join I did Inner join , in that scenario would count(*) work as expected?
I was wondering if in this query
SELECT t.track_id, t.name, i.invoice_id, i.invoice_date
FROM track AS t
LEFT JOIN invoice_line AS il
ON t.track_id = il.track_id
LEFT JOIN invoice AS i
ON il.invoice_id = i.invoice_id AND invoice_date LIKE ‘2020%’
WHERE t.track_id = 1;
instead of left join you used Inner join
SELECT t.track_id, t.name, i.invoice_id, i.invoice_date
FROM track AS t
JOIN invoice_line AS il
ON t.track_id = il.track_id
JOIN invoice AS i
ON il.invoice_id = i.invoice_id AND invoice_date LIKE ‘2020%’
WHERE t.track_id = 1;
Would count(*) have worked and counted only those rows where invoice_date is 2020 ?
hi ,
would it be possible to share a resource for working of join on more than 2 tables because I am struggling with visualizing it. According to me the first time inner join will only show records which are there in track and invoice-line
SELECT t.track_id, t.name, i.invoice_id, i.invoice_date
FROM track AS t
JOIN invoice_line AS il
ON t.track_id = il.track_id’
Result:
Track-id Track name invoice_id invoice_date
1 rock 1 2021
2 punk 2 2020
3 electric 3 2020
4 pop 4 2022
then when we join invoice_line with invoice table we are only going to show records which are having invoice_id same as in invoice_line and invoice date like 2020
it should show only 2 records from the above result
I have mostly understood the concept now I just want to understand
SELECT t.track_id,t.name, count(i.invoice_id) AS no_of_purchases
FROM track AS t LEFT JOIN invoice_line AS il
ON t.track_id=il.track_id
LEFT JOIN invoice AS i
ON i.invoice_id=il.invoice_id
AND i.invoice_date like ‘2020’
GROUP BY t.track_id,t.name
why did we use invoice_id from invoice table and not from invoice_line
