MySQL Queries question

So I have a question that I’m having trouble writing queries for:

  1. What are the names and total spend of customers who made more than one order?

Here are the relevant tables – can anyone point me in the right direction?

I’ll prefix this by saying I’m not an SQL expert, but this should work (although there might be a better way to do it):

SELECT c.firstname,
       c.familyname,
       SUM(i.unitcost * li.quantity) as totalspent
FROM customers c
JOIN orders o ON c.custID = o.custID
JOIN lineitems li ON o.orderID = li.orderID 
JOIN items i ON li.itemID = i.itemID
WHERE c.custID IN (
    SELECT custID FROM orders GROUP BY custID HAVING COUNT(orderID) > 1
)
GROUP BY c.custID;

you realize we’re doing buddy’s homework for him, right? normally that’s frowned upon, and instead we ask him to show us what he tried

that said, your query can be simplified a bit, i think

SELECT c.firstname , c.familyname , SUM(i.unitcost * li.quantity) as totalspent FROM customers c JOIN orders o ON c.custID = o.custID JOIN lineitems li ON o.orderID = li.orderID JOIN items i ON li.itemID = i.itemID GROUP BY c.custID HAVING COUNT(DISTINCT o.orderID) > 1notice in your HAVING clause, orderid was not qualified, didn’t have to be, because there was only one table in your subquery, whereas i had to qualify it, because there are two orderids in the tables

1 Like

@r937 , please. This is not homework. Of course, i 've tried to solve it.
Thank you. I understood why

HAVING COUNT(DISTINCT o.orderID) > 1

But, i will think more about it.

@fretburner, thank you too !!!

I think I’m lost with the qualification. This may be a stupid question (and definately and translation one!) but what do you mean that fretburner didn’t qualify orderdId in his query but you did?

fretburner didn’t have to specify which table the orderID was meant to come from because there was only one table involved, but in Rudy’s example

the query needs to specify what tables the fields are coming from i.e. “li” and “o”

I don’t know why that’s called “qualify” rather than something else, but that’s what it is.

Thanks. It was a translantion issue… and me being thick as usual :slight_smile: