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!