Building and Organizing Complex Queries ERRORS after writing similar code to the answer

Q&A DQ Courses
, ,
#1

Screen Link: https://app.dataquest.io/m/190/building-and-organizing-complex-queries/7/multiple-named-subqueries

WITH 
    customers_india AS
    (
    SELECT
        first_name,
        last_name,
        customer_id
    FROM customers
    WHERE country = 'India'
    ),

total_amt_per_customer AS
    (
    SELECT 
        customer_id
        SUM(total) total_purchases,
    FROM invoice 
    GROUP BY 1
    )
        
SELECT 
    c.first_name || ' ' || c.last_name customer_name,
    t.total_purchases
FROM customers_india c 
INNER JOIN total_amt_per_customer t
ON c.customer_id = i.customer_id
ORDER BY 1
                    
SELECT 
    c.first_name || ' ' || c.last_name customer_name,
    t.total_purchases
FROM customers_india c 
INNER JOIN total_amt_per_customer t
ON c.customer_id = i.customer_id
ORDER BY 1

What I expected to happen: * * customer_name - The first_name and last_name of the customer, separated by a space, eg Luke Skywalker .

  • total_purchases - The total amount spent on purchases by that customer.
  • The results should be sorted by the customer_name column in alphabetical order.

What actually happened: (sqlite3.OperationalError) near "(": syntax error [SQL: WITH customers_india AS ( SELECT first_name, last_name, customer_id FROM customers WHERE country = 'India' ), total_amt_per_customer AS ( SELECT customer_id SUM(total) total_purchases, FROM invoice GROUP BY 1 ) SELECT c.first_name || ' ' || c.last_name customer_name, t.total_purchases FROM customers_india c INNER JOIN total_amt_per_customer t ON c.customer_id = i.customer_id ORDER BY 1] (Background on this error at: http://sqlalche.me/e/e3q8)

#2

Hi @nardobenny0. There are a few errors in the code that caused some errors when I copied and pasted it. You’re getting an error because there’s something wrong with the syntax. Every time I would correct an error, it would give a different error message. I put comments in the code below to show where I got errors. I found each one by looking at the beginning part of the error message, like: (sqlite3.OperationalError) near "(": syntax error. In those cases, there was either an extra comma or missing comma. Other errors would give different messages, but it helped direct my eyes where to look.

A good exercise would be to go through and make one change, run the code, and have a look at the next error message that pops up. This will help train you where to look when you’re encountering the errors so that next time the debugging process will be easier (hopefully!)

I hope this helps!

WITH 
    customers_india AS
    (
    SELECT
        first_name,
        last_name,
        customer_id
    FROM customers		#3. no such table, use customer
    WHERE country = 'India'
    ),

total_amt_per_customer AS
    (
    SELECT 
        customer_id			#1. missing comma
        SUM(total) total_purchases,	#2. unneccessay comma
    FROM invoice 
    GROUP BY 1
    )
        
SELECT 
    c.first_name || ' ' || c.last_name customer_name,
    t.total_purchases
FROM customers_india c 
INNER JOIN total_amt_per_customer t
ON c.customer_id = i.customer_id	#5. what is i? Should it be t?
ORDER BY 1
       
#4. this code is repeated             
SELECT 
    c.first_name || ' ' || c.last_name customer_name,
    t.total_purchases
FROM customers_india c 
INNER JOIN total_amt_per_customer t
ON c.customer_id = i.customer_id
ORDER BY 1
2 Likes
#3

Hello @april.g, thanks taking the time to reply and debug my code. I have made the changes that you recommended and I got the intended results. This is my first time learning SQL and I have had a few challenges. I appreciate your advice regarding the proper way to debug SQL code. Thanks again.

Regards,
Bernard

2 Likes