Can't figure out what's wrong with my SQL query, help please

Screen Link:

My Code:

%%sql

WITH total_tracks AS
            (SELECT COUNT(t.track) total_tracks
               FROM invoice i
              LEFT JOIN invoice line il ON i.invoice_id = il.invoice_id
              INNER JOIN track t ON t.track_id = il.track_id
              WHERE i.billing_country = "USA")
    
SELECT
    g.name,
    COUNT(il.track_id) 'tracks sold',
    ROUND(COUNT(il.tracks_id)/(SELECT total_tracks FROM total_tracks),0)*100 'percent sold'
  FROM
    invoice_line il
 INNER JOIN track t ON t.track_id = il.track_id
 INNER JOIN genre g ON g.genre_id = t.genre_id
 INNER JOIN invoice i ON i.invoice_id = il.invoice_id
 WHERE i.billing_country = "USA"
 GROUP BY 1
 ORDER BY 2 DESC;

What I expected to happen:
A query that returns each genre, with the number of tracks sold in the USA:

  • in absolute numbers
  • in percentages.

What actually happened:

 * sqlite:///chinook.db
(sqlite3.OperationalError) near "il": syntax error
[SQL: WITH total_tracks AS (SELECT COUNT(t.track) total_tracks
               FROM invoice i
              LEFT JOIN invoice line il ON i.invoice_id = il.invoice_id
              INNER JOIN track t ON t.track_id = il.track_id
              WHERE i.billing_country = "USA")
    
SELECT
    g.name,
    COUNT(il.track_id) 'tracks sold',
    ROUND(COUNT(il.tracks_id)/(SELECT total_tracks FROM total_tracks),0)*100 'percent sold'
  FROM
    invoice_line il
 INNER JOIN track t ON t.track_id = il.track_id
 INNER JOIN genre g ON g.genre_id = t.genre_id
 INNER JOIN invoice i ON i.invoice_id = il.invoice_id
 WHERE i.billing_country = "USA"
 GROUP BY 1
 ORDER BY 2 DESC;]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

I’ve tried many different ways of fixing this, but can’t seem to figure it out. Any help is appreciated!

Is this it? (missing underscore between invoice and line → invoice_line)

1 Like

Yes! Thank you so much!