Get all names of users who have placed car orders totalling more than 40000$-CodePudding

I have the following tables:

Cars (CarID, Colour, Price)
CarOrders(CarID, OrderID, AmountOfCars)
Orders(OrderID, UserID)
Users (UserID, Name)

I'd like to query: all names of users who have placed car orders totalling more than 40000 (also considering amount, if a user buys the same car multiple times), alongside the number of these orders. The desired results format is: (Users.name, Number)

I have tried the following:

SELECT * 
FROM Users
JOIN (
    SELECT a.UserID, SUM(a.AmountOfCars*b.Price) total 
    FROM Orders a 
    JOIN Cars b, ON b.CarID == a.CarID, 
    GROUP BY a.UserID
) c ON c.UserID == c.UserID 
WHERE c.total > 20000

CodePudding user response：

SELECT
    U.Name,
    SUM(C.Price * CO.AmountOfCars) AS 'Price'
FROM Users U
JOIN Orders O ON U.UserID = O.UserID
JOIN CarOrders CO ON CO.OrderID = O.OrderID
JOIN Cars C ON C.CarID = CO.CarID
GROUP BY U.Name
HAVING  SUM(C.Price * CO.AmountOfCars) > 40000

CodePudding user response：

You're referencing columns that don't exist in the tables you specify. The Order table doesn't have CarID or amountOfCars. Those are in CarOrders, you have to join with that to relate orders with cars.

ON c.UserID == c.UserID makes no sense at all, that will always be true. You meant ON Users.UserID = c.UserID.

I recommend using aliases that are more mnemonic than a, b, c, etc. Abbreviations for the table names are better.

SELECT u.name
FROM Users AS u
JOIN (
    SELECT o.UserID
    FROM Orders AS o
    JOIN CarOrders AS co ON co.OrderID = o.OrderID
    JOIN Cars AS c on co.CarID = c.CarID
    GROUP BY o.UserID
    HAVING SUM(co.amountOfCars * c.Price) > 40000
) AS o ON o.UserID = u.UserID