Blue Week Special Offer | Brighten your week!
days
hours
minutes
seconds

Control Flow - SQL Fundamentals Part II - Not sure where my answer is wrong

Screen Link:
https://app.dataquest.io/m/592/control-flow/2/if-then-in-sql

My Code:

SELECT invoice_id, total
(CASE

WHEN total IN (total > 10) THEN 'High'
ELSE 'Low' 
 END) AS total_category
 FROM invoice

What I expected to happen:

I expected my code above to be the correct answer

What actually happened:
I received the following output upon pressing submit:

Your query did not select the total column.

As you can see, I included total with the SELECT clause, so I don’t understand why it’s incorrect.

Thanks!

Hi @ratnesh.bh and welcome to the community!

I tried your code and at first glance, I thought there must be a bug for this screen…but when I inspected the results of the query, I realized what was going wrong.

Perhaps formatting your SELECT statement a little might help spot what’s causing the problem:

SELECT invoice_id, 
       total
       (CASE
             WHEN total IN (total > 10) THEN 'High'
             ELSE 'Low' 
        END) AS total_category
  FROM invoice

It’s very subtle, but if you look at your SELECT statement, there is something missing between lines 2 and 3. In fact, the only reason you didn’t get an error for this query is because total is also a function in SQL!

All that said, there is also a problem within your CASE clause as well. We don’t actually need to use IN here because we only want to test if total is greater than 10 or not vs test if total is an element of some list.

Let me know if these hints aren’t enough to sort out your query and we can try something else.

Hi Mike, thanks! I don’t quite understand what I have missed between lines 2 and 3. It seems to match the syntax provided in the lesson. Anyway, I think I followed your additional guidance and after making a change, now I get the error: “your query did not select the total column”.

My code:
SELECT invoice_id, total
(CASE
WHEN (total > 10) THEN ‘High’
ELSE ‘Low’
END) AS total_category
FROM invoice;

What I expected to happen:
I expected this to be the correct answer as it matches the syntax provided in the lesson content.

What happened instead:
Now I’m missing the total_category column in the result.

Thanks!

Yeah, I’m sorry…that hint about something missing between lines 2 and 3 was a bit vague and I found it difficult to steer you in the right direction so I’ll just come right out and say it: you’re missing a , after total. Try to remember that a CASE statement is actually just another feature or column in your SELECT clause and so it needs to be separated by a comma.

I figured out this was your problem because your original code produced a result with only two columns but should have produced three. The two it produced were: invoice_id and TOTAL (CASE WHEN ...) AS total_category.

Adding a comma after total should fix it.

1 Like