Query returning wrong value when doing an INNER JOIN?

Question

I have three tables I want to join to calculate the balance = (purchase - payments) for a certain (party_id):

• Party (party_id, type, name)
• Purchase (purchase_id, supplier_id, quantity, rate, total)
• Payments (payment_id, party_id, amount)

The total purchase from that id = 20,000 and its total payments = 15,000 so its balance should be = 5,000. After using this code:

SELECT 
    ((sum(purchase.total)) - (sum(payments.amount))) AS Party_Balance 
FROM 
    Purchase 
INNER JOIN 
    Party 
    ON purchase.supplier_id = party.party_id 
INNER JOIN 
    Payments 
    ON party.party_id = payments.party_id 
WHERE 
    payments.party_id = enter_party_id;

The output is incorrect: 1,000,245 instead of 5,000.

This is how I set up the relationship, is there a relation that should be fixed?

Answer 1

You must do 2 separate groupings of the table Party:
The 1st with Purchase to get the SUM of total
and the 2nd with Payments to get the SUM of amount.
Then join the 2 subqueries and get Party_Balance:

SELECT pur.party_id,  pur.total - pay.sumamount AS Party_Balance FROM (
  SELECT Party.party_id, SUM(Purchase.total) AS total
  FROM Party INNER JOIN Purchase ON Party.party_id = Purchase.supplier_id
  GROUP BY Party.party_id
) AS pur
INNER JOIN (
SELECT Party.party_id, SUM(amount) AS sumamount
  FROM Party INNER JOIN Payments ON Party.party_id = Payments.party_id
  GROUP BY Party.party_id
) AS pay
on pay.party_id = pur.party_id