CODEWITHSUNDEEP
sqlms-access
Zekeria
Zekeria

Reputation: 11

Returning wrong Sum() values when outer joining multiple tables at the same time

I have this problem in ms access while im trying to select values from different tables while joining them with the same one.

SELECT 
    Customers.ID, Customers.Name, Customers.Address, Customers.Phone, 
    SUM(Order_Lines.Subtotal) AS [Total Balance],    
    SUM(Order_Payements.Amount) AS [Payments Total]
FROM 
    ((Customers 
LEFT JOIN 
    Orders ON Customers.ID = Orders.Customer_Id) 
LEFT JOIN 
    Order_Lines ON Orders.ID = Order_Lines.Order_ID) 
LEFT JOIN 
    Order_Payments ON Orders.ID = Order_Payments.Order_ID
GROUP BY 
    Customers.ID, Customers.Name, Customers.Address, Customers.Phone;

Theses are my tables:

enter image description here

This is my data:

Order lines:

enter image description here

Orders:

enter image description here

Orders Payments:

enter image description here

After a lot of research, I found out that the records are being selected multiple times, exactly the same amount that I have order line for the order. for example if I have 3 order lines the amount will be 3x the original amount etc.

What is the best way to select the data and get right values?

Upvotes: 1

Views: 75

Answers (2)

Iceberg
Iceberg

Reputation: 21

Your problem is when you joining tables order_lines and order_payments use same column parameter Order.ID for this reason when you join second table SUM(values) affect all row include Order.ID same. Then your values looks like a double it.

You can use like this

SELECT 
    Customers.ID, Customers.Name, Customers.Address, Customers.Phone,
    (SELECT 
         SUM(Order_Lines.Subtotal)
     FROM 
         Order_Lines 
     WHERE
         Orders.ID = Order_Lines.Order_ID 
     GROUP BY 
         Order_ID) AS 'Total Balance' ,
    (SELECT 
         SUM(Orders_Payments.Amount)
     FROM 
         Orders_Payments 
     WHERE
         Orders.ID = Orders_Payments.Order_ID 
     GROUP BY 
         Order_ID) As 'Payments Total'
FROM 
    Orders 
LEFT JOIN 
    Customers ON Customers.ID = Orders.Customer_Id;

Or you can use subquery then Left Join like this

SELECT 
    Customers.ID, Customers.Name, Customers.Address, Customers.Phone,
    (SELECT SUM(Order_Lines.Subtotal)
     FROM Order_Lines 
     WHERE Orders.ID = Order_Lines.Order_ID 
     GROUP BY Order_ID) AS 'Total Balance',
    SUM(Orders_Payments.Amount)
FROM 
    Orders 
LEFT JOIN 
    Customers ON Customers.ID = Orders.Customer_Id
LEFT JOIN 
    Orders_Payments ON Orders_Payments.Order_ID = Orders.ID 
GROUP BY 
    Customers.ID, Customers.Name, Customers.Address, Customers.Phone, Orders.ID

Upvotes: 0

Zekeria
Zekeria

Reputation: 11

I found a way to do it, I selected each record alone then I joined the two select statements together. this is the code:

SELECT
    TP.ID, TP.Name, TP.Address, TP.Phone, TP.Payments_Total,
    OW.[Total Ows], OW.[Total Ows] - TP.Payments_Total AS Balance 
FROM
    (SELECT 
         Customers.ID, Customers.Name, Customers.Address, 
         Customers.Phone, 
         SUM(Order_Payments.Amount) AS Payments_Total
     FROM 
         (Customers
     LEFT JOIN 
         Orders ON Customers.ID = Orders.Customer_Id) 
     LEFT JOIN 
         Order_Payments ON Orders.ID = Order_Payments.Order_ID
     GROUP BY 
         Customers.ID, Customers.Name, Customers.Address, Customers.Phone) AS TP
INNER JOIN
    (SELECT 
         Customers.ID, 
         SUM(Order_Lines.Subtotal) AS [Total Ows]
     FROM 
         (Customers
     LEFT JOIN 
         Orders ON Customers.ID = Orders.Customer_Id) 
     LEFT JOIN 
         Order_Lines ON Orders.ID = Order_Lines.Order_ID
     GROUP BY 
         Customers.ID, Customers.Phone) AS OW ON TP.ID = OW.ID

It looks a bit messy sorry for that but I'm not familiar with stack overflow yet.

Upvotes: 0

Related Questions