I may be trying to do too much in a single query here, but it would be nice if I could get it working! Apparently, the version of MySQL used by my work does not support the WITH ROLLUP feature, otherwise I think that would work.
What I have are 3 tables: gc_info, amends, payments.
'gc_info' contains a number that represents the original award given to a contractor. If there are any increases, these are kept in 'amends', and all payments are kept in 'payments'.
What I need to do is get three numbers: current total award (award+amendments), current total paid (SUM(payments)), and current balance ((awards + SUM(amendments)) - SUM(payments)).
This almost works with the below query, except that the where there should only be 1 amendment, 11 more show up, or one for each of the 12 payments made to the contractor:
SELECT org_name,FORMAT(award+amend,2) AS current_total, FORMAT((award+SUM(amend))-SUM(paid),2) AS balance, FORMAT(SUM(paid),2) AS total_paid FROM contact_info LEFT JOIN gc_info ON contact_info.id=gc_info.contact_id LEFT JOIN amends ON gc_info.gc_number=amends.gc_number LEFT JOIN payments ON gc_info.gc_number = payments.gc_number WHERE gc_info.gc_number = "06-8479" GROUP BY gc_info.gc_number
For example, on a contract with an original award of 168,000, one amendment of 168,000, and 12 payments of 14,000, this gives me:
|org_name|current_total |balance |total_paid |
|Org Name|336,000.00 (correct)|2,016,000.00 (incorrect)|168,000.00 (correct)|
So in FORMAT((award+SUM(amend))-SUM(paid),2), SUM(amend) is adding up 12 instances of amend instead of 1.
Any advice would be helpful. I've read through the JOIN section in the MySQL manual, tried different JOINs, GROUP BYs, etc. but cannot figure this one out.
Thank you very much,
Jason
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw
