MySQL Calculation

[NomadicJosh]

I am trying 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. The query works until I add the left join course_sec line, then it just throughs it off. 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 remaining balance. However, if a payment is present, the balance for that particular student ends up being wrong. The amount becomes higher instead of lower.

 

Below is my query, and I've created a SQLfiddle (http://sqlfiddle.com/#!2/3f350e/1) with the necessary tables. I hope someone here can help me figure out what I am doing wrong. Thanks in advance.

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

[Psycho]

Can you explain in lay terms what you are trying to accomplish? And can you explain the purpose for each table (e.g. bill vs. billing_table)?

 

I think there are some problems with your database schema. For example, why do you need two columns to JOIN some of the tables?

FROM bill a
LEFT JOIN student_fee b
  ON a.stuID = b.stuID
 AND a.ID = b.billID

I would think that the student ID only needs to exist in one of those tables - not both - and that they could be JOINed using just the bill ID. Can multiple students be associated with the same bill record? Definitely seems you have duplicated data throughout your tables making this more complicated than it needs to be.

 

FYI: I'll also add that using aliases in a query (i.e. LEFT JOIN student_fee b) is there to add clarity. Using values like a, b, c, d, etc. only makes it harder.

Edited March 25, 2014 by Psycho

[NomadicJosh]

What I want to do is run a report showing what students haven't paid their student bill in full grouped by term.

 

billing_table - Allows financial department to set up a billing table of fees that can be used in creating a bill/invoice for students

bill - A bill is created for each student based on registered term; grouped by student ID and term

student_fee - a list of fees (billing_table FK) connected with a student's bill; grouped by student ID and term

payment - Payments submitted by the student and placed towards their bill; grouped by student ID and term

course_sec - Courses offered by the institution. Course fee, lab fee, and material fee are placed on course sections; grouped by term

stu_course_sec - When a student registers for a course_sec a student course section record is created; grouped by student ID and term

[Psycho]

So, your problem is solved? Don't know why you marked it as such.

 

Thanks for the additional info, but I still don't see an explanation for the duplication of the data in the tables. I have never seen two tables that are joined by two separate columns. Not saying that wouldn't necessarily be valid, but it doesn't seem so in this case and it is creating a much more complicated configuration.

 

For example, you have 'term' located in a lot of these tables. That doesn't make sense. You should have a record when a student registers for a course that has a reference to the course and the term that they registered for. The billing table should only include a reference to the table that holds the registration record and NOT include a reference to the term since that can be derived from the registration info.

 

I would love to help, but the configuration of the tables is making this more complicated than I am willing to invest time into. I suggest you review some tutorials on Database Normalization if you want to clean up the database schema.

[NomadicJosh]

Yep, solved it and updated fiddle.

[NomadicJosh]

I guess I should post what I've updated: http://sqlfiddle.com/#!2/3d8fa/1