wrong value on mysql INNER JOIN

I am getting the wrong value开发者_JAVA技巧 on gfee and netpay.

SELECT s.id, s.name, c.name AS course_name,
s.open_bal AS open_balance, sum(i.amount) AS gross_fee,
sum(i.discount) AS discount, sum(i.amount) - sum(i.discount) AS net_payable,
SUM(r.reg_fee+r.tut_fee+r.other_fee) AS net_recieved,
(sum(i.amount) - sum(i.discount)) - SUM(r.reg_fee+r.tut_fee+r.other_fee) AS balance_due
FROM subscribers s
INNER JOIN courses c on c.id = s.course_id
LEFT JOIN invoices i on i.student_id = s.id
LEFT JOIN recipts r on r.student_id = s.id
GROUP BY s.id;

Why is this happening?

---

SELECT s.id
     , s.name
     , c.name AS course_name
     , s.open_bal AS open_balance
     , igroup.gross_fee
     , igroup.discount
     , igroup.net_payableinvoices
     , rgroup.net_recieved
     , igroup.net_payableinvoices - rgroup.net_recieved
       AS balance_due
FROM students s
INNER JOIN courses c
  on c.id = s.course_id
LEFT JOIN 
  ( SELECT i.student_id
         , SUM(i.amount) AS gross_fee
         , SUM(i.discount) AS discount
         , SUM(i.amount) - sum(i.discount)
           AS net_payableinvoices
    FROM invoices i
    GROUP BY i.student_id
  ) AS igroup
  ON igroup.student_id = s.id
LEFT JOIN 
  ( SELECT r.student_id
         , SUM(r.reg_fee+r.tut_fee+r.other_fee)
           AS net_recieved
    FROM recipts r 
    GROUP BY r.student_id
  ) AS rgroup
  ON rgroup.student_id = s.id
;

---

The most likely cause of your problem is multiple rows existing in one of the joined tables.

Since you are getting exactly twice your expected value (70000 vs 35000) I would guess that there are two rows in either the courses or recipts tables with student_id=22.

---

The fact that 70,000 is double 35,000 is a hugely important clue. You're talking about a SUM and you're getting twice what you expected. That suggests strongly that you're joining to twice as many rows as you think you are.

Without knowing the details of your schema it's hard to be specific, but if you're joining to two different rows in receipts for each row in invoices (for this particular record), you end up with two 35,000 entries included in your sum. Not what you had in mind.