Question about guided project with SQL

Hi everyone! So I have this goal to write a query that returns each genre, with the number of tracks sold in the USA (absolute numbers and percentages).
I solved like this for the moment

My Code:

SELECT COUNT(il.invoice_line_id) total_purchases,
       c.country
  FROM invoice_line il
INNER JOIN invoice i ON i.invoice_id = il.invoice_id  
INNER JOIN customer c ON c.customer_id = i.customer_id
WHERE c.country = 'USA';

First I calculated the actual number of tracks sold in the USA, then I wrote the other query

SELECT c.country,
       g.name genre_name,
       SUM(il.quantity) tracks_sold,
       ROUND(CAST(SUM(il.quantity) AS FLOAT) / 1051 * 100, 2) percentage
  FROM invoice_line il
INNER JOIN invoice i ON i.invoice_id = il.invoice_id
INNER JOIN customer c ON c.customer_id = i.customer_id
INNER JOIN track t ON t.track_id = il.track_id
INNER JOIN genre g ON g.genre_id = t.genre_id
WHERE c.country = 'USA'
GROUP BY 1, 2
ORDER BY 3 DESC;

It seems to work to me, but I was wondering if there’s a way to connect the two queries into one.
I tried this:

%%sql
SELECT c.country,
       g.name genre_name,
       SUM(il.quantity) tracks_sold,
       CAST(SUM(il.quantity) AS FLOAT) / (
                                        SELECT 
                                          COUNT(il.invoice_line_id) total_purchases,
                                          c.country
                                          FROM invoice_line il
                                    INNER JOIN invoice i ON i.invoice_id = il.invoice_id  
                                    INNER JOIN customer c ON c.customer_id = i.customer_id
                                    WHERE c.country = 'USA'
       )
           FROM invoice_line il
INNER JOIN invoice i ON i.invoice_id = il.invoice_id
INNER JOIN customer c ON c.customer_id = i.customer_id
INNER JOIN track t ON t.track_id = il.track_id
INNER JOIN genre g ON g.genre_id = t.genre_id
WHERE c.country = 'USA'
GROUP BY 1, 2
ORDER BY 3 DESC;

But I get an error message like this:

(sqlite3.OperationalError) sub-select returns 2 columns - expected 1
[SQL: SELECT c.country,
       g.name genre_name,
       SUM(il.quantity) tracks_sold,
       CAST(SUM(il.quantity) AS FLOAT) / (
                                        SELECT 
                                          COUNT(il.invoice_line_id) total_purchases,
                                          c.country
                                          FROM invoice_line il
                                    INNER JOIN invoice i ON i.invoice_id = il.invoice_id  
                                    INNER JOIN customer c ON c.customer_id = i.customer_id
                                    WHERE c.country = 'USA'
       )
           FROM invoice_line il
INNER JOIN invoice i ON i.invoice_id = il.invoice_id
INNER JOIN customer c ON c.customer_id = i.customer_id
INNER JOIN track t ON t.track_id = il.track_id
INNER JOIN genre g ON g.genre_id = t.genre_id
WHERE c.country = 'USA'
GROUP BY 1, 2
ORDER BY 3 DESC;]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

Would someone know how to help me? And also, in general, does what I did until now look to have sense for you?

Thanks!!!

There are others here who are much better with SQL than I am and could give you a much better review of your code/approach but what I can tell you is why you are getting this particular error…

In your outer query, you are trying to divide a quantity (CAST(SUM(il.quantity) AS FLOAT)) by two columns from your subquery (COUNT(il.invoice_line_id) total_purchases and c.country).

CAST(SUM(il.quantity) AS FLOAT) / (
                                        SELECT 
                                          COUNT(il.invoice_line_id) total_purchases,
                                          c.country
                                          FROM invoice_line il
                                    INNER JOIN invoice i ON i.invoice_id = il.invoice_id  
                                    INNER JOIN customer c ON c.customer_id = i.customer_id
                                    WHERE c.country = 'USA'
       )

I believe you can simply remove c.country from your select clause here and it should run without error. If you were to include a link to the lesson, I could try playing around with your code to get a better sense of things. In fact, you may want to look at this post for some great tips on asking technical questions in the community.

Let me know how you make out with my suggestion.

As for a review of your approach, perhaps @Bruno could provide a more knowledgeable response.

1 Like

You were right!!
And I’m happy coz I figured out alone before your answer :smiley: :smiley:
Still, thank you a lot, Math!

1 Like