1

I'm stucked for a long while and unable to find whats wrong with my query, there is something wrong with my debit column in LEFT JOIN which is show in Query Result image in given below. The actual value of my debit table is 500 but MYSQL query display as 1500. What am I doing wrong here. Kindly help me please.

This is my customers table

customers` table

This is my cust_credit table

cust_credit table

This is my cust_debit table

cust_debit table

MYSQL Query is given following

SELECT 
    customers.id as id, 
    customers.cust_name AS customer_name,
    SUM(cust_debit.debit_amount) as debit,
    SUM(cust_credit.credit_amount) as credit,
    (SUM(cust_debit.debit_amount)) - (SUM(cust_credit.credit_amount)) as balance


    FROM customers
  LEFT JOIN  cust_debit  ON customers.id = cust_debit.cust_id
  LEFT JOIN  cust_credit ON customers.id = cust_credit.cust_id


  GROUP BY customers.id
  ORDER BY customers.id

My Query Result is given following

Result

Student
  • 141
  • 9
  • 1
    You are adding up the cust_debit.debit_amount columns, for customer one there are 3 records with 500 value. results in 1500 – Muhammad Saqlain Dec 12 '17 at 20:02
  • **SELECT customers.id as id, customers.cust_name AS customer_name, SUM(cust_debit.debit_amount) as debit FROM customers LEFT JOIN cust_debit ON customers.id = cust_debit.cust_id GROUP BY customers.id ORDER BY customers.id** than why this works fine ? – Student Dec 12 '17 at 20:07

1 Answers1

0

You have multiple rows in the credit table, this makes multiple rows before the group by which causes the problem. You can see this if you take out the group by and select all columns.

If you group by in a sub join this problem goes away then you have one row per customer before the group by.

SELECT 
  customers.id as id, 
  customers.cust_name AS customer_name,
  SUM(cust_debit.debit_amount) as debit,
  SUM(cust_credit.credit_amount) as credit,
  (SUM(cust_debit.debit_amount)) - (SUM(cust_credit.credit_amount)) as balance
FROM customers
LEFT JOIN cust_debit  ON customers.id = cust_debit.cust_id
LEFT JOIN (
  SELECT cust_id, sum(credit_amount) as credit_amount)
  from cust_credit
  group by cust_id
) cust_credit ON customers.id = cust_credit.cust_id
GROUP BY customers.id
ORDER BY customers.id
Hogan
  • 69,564
  • 10
  • 76
  • 117