Building and Organizing Complex Queries (3/10)

Hello Everyone,

It took quite a while and many attempts, but I finally got this activity with one exception: I was using COUNT(*) in the main query to return the number_of_tracks, but this resulted in several playlist’s having a 1 in the number_of_tracks column rather than a 0. I needed to use COUNT(track_name) instead which produces the desired result.

My question is: why did COUNT(*) return a 1 for those playlists that had no tracks?

Thanks!
Scott

Hey Scott!

COUNT(*) counts the total amount of rows, or records in the table you’re querying. A value of “0” to signify no tracks would still count as a row for this purpose.

What you were interested in instead was the total amount of tracks, which is why it would be better to explicitly specify that using COUNT(track_name).

Thanks for the response! That makes more sense!

Hello again,

Sorry, I had one other question on the next lesson (Creating Views): I am not clear as to the distinction between using [database name].[view or table name] and just using [view or table name]. I read the SQLite documentation on ATTACH DATABASE, but I’m still not clear on why or when we use one version over the other. Any explanations would be great!

Thanks!
Scott

From what I gather, it has to do with the chinook database file having been manually attached to the database connection.

Additionally, if you experiment with the solutions, you’ll find that the only time you’re required to specify the database name is when you intend to create the view in this manually attached database.

For instance, the following will work, even though I’m only using the database name once:

CREATE VIEW chinook.customer_gt_90_dollars AS 
    SELECT
        c.*
    FROM invoice i
    INNER JOIN customer c ON i.customer_id = c.customer_id
    GROUP BY 1
    HAVING SUM(i.total) > 90;
SELECT * FROM customer_gt_90_dollars;

Only the very first line uses the database name. This quote from the documentation should help shed light on why we didn’t have to specify the database name more than once:

So while we needed to specify the database name when we were creating the view (again, this is because the database was manually attached to the database connection), it wasn’t necessary to specify the database name thereafter because the table names we were using only existed in that database.

That makes more sense, thank you so much for your help!

1 Like