Screen Link:
https://app.dataquest.io/m/190/building-and-organizing-complex-queries/4/creating-views
My Code:
CREATE VIEW chinook.customer_gt_90_dollars AS
SELECT *
FROM chinook.customer AS c
INNER JOIN chinook.invoice AS i ON i.customer_id = c.customer_id
GROUP BY i.customer_id
HAVING SUM(i.total) > 90;
SELECT *
FROM chinook.customer_gt_90_dollars;
What I expected to happen: I expected this would return a table with the list of customers who made purchases of more than USD90
What actually happened:
The table returned, but it contained list of customers (and all related columns from “customer” table) with purchases of less than 90 dollars. See the screenshot below:
Replace this line with the output/error
I have compared my code with the correct answer and I only see a difference in that I have changed the order of joining the tables (“customer” and “invoice”) in INNER JOIN part. What I did is (you can see that in my code) I have selected all columns in “customer” table (as the assignment said: " Create a view called customer_gt_90_dollars: * The view should contain the columns from customers , in their original order.") and then I joined this table with the “invoice” table. Later I have grouped everything by “i.customer_id” but the table returned did not meet the >90 criterion. The difference with the correct answer in my view is only the order of joining the tables. Does it make such a huge difference? OR am I missing something important here?
Thanks for your assistance in this!
