Retrieve columns from three tables-CodePudding

I want to use columns from three different tables and use them to calculate how much each customer has ever spent.

tblCustomer(CustomerID)

tblOrder(CustomerID, ProductID, Amount)

tblProduct(ProductID, Price)

So I want to filter out the orders made by a customer, check what product they ordered and what amount of it, check the price and multiply it with the amount they ordered and do that with every customer

expected result:

CustomerID | TotalSpent

1|20

2|130

CodePudding user response：

This depends on the relationship between the tables. The below assumes there is only one price per product in the price table. The customer table is excluded as it is unnecessary but could be joined again using the customerID

SELECT tblOrder.CustomerID,
SUM(tblOrder.Amount*tblProduct.Price) AS 'TotalSpent'
FROM tblOrder JOIN tblProduct ON tblProduct.ProductID = tblOrder.ProductID
GROUP BY tblOrder.CustomerID