GUIDED PROJECT : Answering Business Questions using SQL

Screen Link: https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/5/analyzing-sales-by-country

My Code:

SELECT c.country , count(c.customer_id)
 FROM customer c
INNER JOIN invoice iv ON iv.customer_id = c.customer_id
GROUP BY 1 ORDER BY 2 DESC
 LIMIT 5

What I expected to happen:

doubt2

What actually happened:

doubt13

WHY THERE IS AN INCREASE IN COUNT OF CUSTOMERS, EVEN THOUGH I SELECTED DATA ONLY FROM CUSTOMERS TABLE

Easy on the caps lock, please.

Once you JOIN two tables, the output table changes based on how you joined them. Anything you select from thereafter is dependent on that joined table and not the original tables.

SELECT c.country , c.customer_id
 FROM customer c

The above will result in a table that looks like this -

image

Your code, before the grouping -

SELECT c.country , c.customer_id
 FROM customer c
INNER JOIN invoice iv ON iv.customer_id = c.customer_id

will return the following -

image

The c.country is no longer accessing the column from your customer table.

The c.country is accessing the equivalent of that column (from the customer table) from this new, joined table. The c.country is to provide the necessary distinction between which columns you are selecting.

I would recommend going through the lessons on joining again just to be sure of what happens when you join and select from that join. And always take smaller steps for this, at least for the time being. Break things down, and view the output of each step before jumping into the next step of the query. Helps to build a better mental model of what each query does. SQL has a bit of a steep learning curve.

1 Like

Thanks for your response

I still have a doubt, as we have only one country column after joining the table with invoice table, why
there is an increase in the number of countries after joining the table

Please explain in detail by giving the view of columns before and after joining the table and which column is accessed by c.country

I won’t be doing that. You can do that already.

As I have pointed out, go through the introductory Missions on SQL Joins again. Print out values at every step and think about what’s happening and why. Think about what a JOIN does. If you then have specific questions, feel free to create a new post about it.

1 Like