SQL creating views question

Q&A DQ Courses
, , ,
#1

Screen Link:

Hey all,

Bit stuck on this question.

My Code: 

DROP VIEW IF EXISTS chinook.customer_gt_90_dollars;

CREATE VIEW chinook.customer_gt_90_dollars AS
    SELECT * FROM chinook.customer
     WHERE (SELECT SUM(total) > 90
             FROM invoice);
     
SELECT * FROM chinook.customer_gt_90_dollars;

What actually happened: I ended up with a 59 rows, instead of 18 (as the provided answer gets).

Apparently what I am supposed to do is:

CREATE VIEW chinook.customer_gt_90_dollars AS
    SELECT
        customer.*
    FROM chinook.invoice AS i
    INNER JOIN chinook.customer ON i.customer_id = customer.customer_id
    GROUP BY 1
    HAVING SUM(i.total) > 90;

Now, I totally understand that the above will provide the correct result. My first thought was to actually do an inner join as above, butt I am confused as to how my initial attempt didn’t provide a shortcut?

Could someone please explain this for me?

Many thanks!

#2

What do you think your following statement does?

You are summing total above and comparing it with 90. What’s the output for that? Is that what you need? Does that give you customers who have spent more than 90 or does it output something else?

Try to break down your own logic to identify what the issue might be. Feel free to ask more questions based on what you think.

#3

so it will include all entries as it SUMs all entries in the subquery and compares it with 90?

and

GROUP BY 1
    HAVING SUM(i.total) > 90;

Does this calculate the SUM for all entries that corresponds to each ‘GROUP BY 1’ (customer id) then compares that to 90? so this is actually several calculations based on each customer id?

#4

Do you think it does that? Did you try to run it separately from the rest? Try to first test that out and see what it outputs.

That is correct. It’s checking for which customers spent 90 or more in total. You can’t find the total money spent by each customer without grouping them.