MySQL Calculation

[joshmac]

I am tring to perform a calculation in my query. I've been working on it for hours, so I need another pair of eyes to see what I am missing. Everything in the query is working except for the g.courseFee part. The course fee is not being added to the calculation. I am not sure if the issue is in the select clause or the left join clause. Below is my query and any help is greatly appreciated:

SELECT COALESCE(SUM(c.amount),0)+COALESCE(SUM(g.courseFee),0) AS 'Balance',
a.termCode as 'Term',
a.stuID AS 'Student ID',
CONCAT(d.lname, ', ', d.fname) AS 'Student Name',
a.comment AS Comment
FROM bill a
LEFT JOIN student_fee b on a.stuID = b.stuID AND a.ID = b.billID
LEFT JOIN billing_table c ON b.feeID = c.ID
LEFT JOIN person d ON a.stuID = d.personID
LEFT JOIN payment e on a.stuID = e.stuID and a.termCode = e.termCode
LEFT JOIN stu_course_sec f ON a.stuID = f.stuID AND e.termCode = f.termCode
LEFT JOIN course_sec g ON f.termCode = g.termCode AND f.courseSecCode = g.courseSecCode
GROUP BY a.stuID,a.termCode

[iamthwee]

any chance of dumping this on sqlfiddle to test

[joshmac]

Yes, sorry that I did not think of that. So here is the link to the sqlfiddle: http://sqlfiddle.com/#!2/3f350e/1

Also, I've updated the query because I did not include the person table in fiddle:

SELECT COALESCE(SUM(c.amount),0)+COALESCE(SUM(g.courseFee),0)*-1+COALESCE(SUM(e.amount),0) AS 'Balance',
a.termCode as 'Term',
a.stuID AS 'Student ID',
a.comment AS Comment,
e.amount AS 'Last Payment',
e.dateTime AS 'Last Payment Date'
FROM bill a
LEFT JOIN student_fee b on a.stuID = b.stuID AND a.ID = b.billID
LEFT JOIN billing_table c ON b.feeID = c.ID
LEFT JOIN payment e on a.stuID = e.stuID and a.termCode = e.termCode
LEFT JOIN stu_course_sec f ON a.stuID = f.stuID AND e.termCode = f.termCode
LEFT JOIN course_sec g ON f.termCode = g.termCode AND f.courseSecCode = g.courseSecCode
GROUP BY a.stuID,a.termCode

[joshmac]

Also, I should mention that I've added *-1 in order to make it a negative balance and added +COALESCE(SUM(e.amount),0) after it to subtract any payments made and give me the remainding balance. However, when there is a payment made, the balance for that particular student ends up being wrong. The amount becomes higher instead of lower.