CODEWITHSUNDEEP
mysqlsql
deadpixels
deadpixels

Reputation: 819

SQL join tables, total spent by all customers

I'm a newbie at SQL and this is the question I've been struggling with:

I have these tables:

customers(custID, firstname, familyname)

items(itemID, unitcost)

lineitems(quantity, orderID, itemID)

orders(orderID, custID, date)

I need to find the names and total spend of all customers that made more than one order.

SELECT SUM(items.unitcost*lineitems.quantity) AS "total_spent"
FROM orders
INNER JOIN customers
ON orders.custID=customers.custID
GROUP BY firstname
HAVING COUNT(DISTINCT orderID)>1
LIMIT 0,30

Upvotes: 0

Views: 1660

Answers (1)

sgeddes
sgeddes

Reputation: 62851

I think you just need to continue your joins:

SELECT c.custId, c.firstname, SUM(i.unitcost*li.quantity) total_spent
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, c.firstname
HAVING COUNT(DISTINCT o.orderID)>1
LIMIT 0,30

Upvotes: 2

Related Questions