Similar Query gives different results

Q&A
, ,
#1

Screen Link:

https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/3/selecting-albums-to-purchase

My Code: 

SELECT 
    g.name AS genre,
    COUNT(il.track_id) AS number_of_tracks
FROM invoice AS i 
INNER JOIN invoice_line AS il ON il.invoice_id = i.invoice_id 
INNER JOIN track AS t ON t.track_id = il.track_id
INNER JOIN genre AS g ON g.genre_id = t.genre_id
WHERE i.billing_country = "USA" AND
    g.name LIKE LOWER("%hip%") OR
    g.name LIKE LOWER("%punk%")OR
    g.name LIKE LOWER("%pop%") OR
    g.name LIKE LOWER("%blues%")
GROUP BY g.name
ORDER BY number_of_tracks DESC

%%sql 
SELECT 
    g.name,
    COUNT(g.name)
FROM invoice AS i 
INNER JOIN invoice_line AS il ON il.invoice_id = i.invoice_id 
INNER JOIN track AS t ON t.track_id = il.track_id
INNER JOIN genre AS g ON g.genre_id = t.genre_id
WHERE i.billing_country = "USA"
GROUP BY g.name

What I expected to happen:
Both queries are giving different results, I mean the count of tracks, In on query m getting count as 130 for punk

whereas in other m getting 492 for punk,
unable to understand why this is happening,

In my sense both should have given same results

What actually happened:

image

image

Kindly help

#2

It’s because of the above.

You are assuming the above is doing the following -


Country is USA AND (Genre name is one of these)

What’s actually happening is -

(Country is USA AND Genre name has "hip" in it) OR (genre name is one of the remaining ones)

That’s why using parenthesis is important for such statements. For both of your queries to be equivalent you will need to do something like -

WHERE i.billing_country = "USA" AND
    (g.name LIKE LOWER("%hip%") OR
    g.name LIKE LOWER("%punk%") OR
    g.name LIKE LOWER("%pop%") OR
    g.name LIKE LOWER("%blues%"))

See where I have placed the additional parenthesis above.

1 Like
#3

@the_doctor Thanks I was’nt aware that it will require parenthesis, Thank you for correcting, I struggled for 3 hours for a small little thing :stuck_out_tongue: :smiley: