Less Common Joins - Q3

Screen Link: Learn data science with Python and R projects

My Code:
SELECT, SUM(il.unit_price * il.quantity) AS total_rev
FROM genre g
LEFT JOIN track t
ON g.genre_id = t.genre_id
LEFT JOIN invoice_line il
ON t.track_id = il.track_id

What actually happened:

Is incorrect because:

  • The resulting table does not contain all columns that we expected.

Hi @faeezmohamed. I’ve spent a good hour and a half (since you posted it) banging my head against the wall over this problem but I finally figured it out…and honestly, I have no clue how I came up with the solution…something buried deep in my brain somewhere, apparently!

It came to me suddenly when looking at the result table of your query above; I noticed some lines were blank due to NULL values. I thought maybe the auto-grader would like 0s in their place?

I vaguely recall reading an article about SUM vs TOTAL and how there’s only a tiny difference between them: how they handle NULL values. So I simply swapped the function SUM for TOTAL and it worked!

■■■… I spent the past 2 hrs trying to figure out where I went wrong. Thank you so much!

Hey, no worries…I was here doing the same thing! :laughing:

Funny. Cool solution. Our intention was to have users use a CASE clause to handle the null values :sweat_smile:

1 Like

Too funny…cause after I saw that it worked (I’m still shocked that I remembered the TOTAL function even existed) I thought: “DQ obviously did not want us to solve it this way…but clearly 0 is preferable over NULL…so how would I do it without using TOTAL?” At which point it hit me: use a CASE clause!

Having implemented both solutions now, I have to admit, I like my version more :sunglasses: