So I have a question that I’m having trouble writing queries for:
- 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?
So I have a question that I’m having trouble writing queries for:
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) > 1
notice 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
@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