4. Combining Multiple Joins with Subqueries. COUNT(*) VS COUNT(il.quantity) IT WORKS!

  1. Combining Multiple Joins with Subqueries

Screen Link: Learn data science with Python and R projects

Greetings

Studying the code I asked myself the following question:

  • Why should there be an asterisk in count when determining inside the count(il.quantity) we get the same result?

in fact the DQ platform validates it as good and in my opinion its easier to understand. :thinking:

My code:

SELECT ta.album,
        ta.artist,
        COUNT(il.quantity) tracks_purchased 
        
    FROM invoice_line il
    INNER JOIN (
                SELECT 
                        t.track_id,
                        al.title album,
                        ar.name artist
                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
                ) ta
                ON ta.track_id = il.track_id
    GROUP BY 1
    ORDER BY 3 DESC LIMIT 5;

This is the Solution Code:

SELECT
    ta.album_title album,
    ta.artist_name artist,
    COUNT(*) tracks_purchased
FROM invoice_line il
INNER JOIN (
            SELECT
                t.track_id,
                al.title album_title,
                ar.name artist_name
            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
           ) ta
           ON ta.track_id = il.track_id
GROUP BY 1, 2
ORDER BY 3 DESC LIMIT 5;

Possibly I’m missing something and I’m not able to see it.

I would appreciate some light

Thanks again.

A&E

1 Like

Hi @Edelberth

This link might help: In SQL, what’s the difference between count(column) and count(*)? - Stack Overflow

2 Likes

The truth is that I feel a little embarrassed, before asking a question here I usually look everywhere even under the carpet, but apparently I must have been so confused with the battle I had between SQL and my raspberry that I did not even realize.

I apologize and thank you for the search you did for me.

Thank you.

A&E

1 Like

Hi @Edelberth

Dude chill! I could see from the previous posts that you do prefer to figure out stuff on your own before you drop in here. And this topic didn’t take any effort.

It was actually my own interest as I never thought about this myself. It’s more like a catchy interview question but to our disadvantage it shows the future employer that we sometimes ignore the little but obvious details. So thanks for posting!

Edit: Even if we know thing technically/ conceptually we may not know how to put them in words! So searching out these kind questions now and then really helps.

1 Like

Post must be at least 20 characters:

2 X :heart:

A&E.