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/[EMAIL PROTECTED]
Try the following (not sure why, but this is how I set my queries up)....
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 contact_info.gc_number=amends.gc_number LEFT JOIN payments ON contact_info.gc_number = payments.gc_number WHERE contact_info.gc_number = "06-8479" GROUP BY contact_info.gc_number
I always use the same field (from 1st table) when I do JOIN / GROUP BY / WHERE / etc. This way I know I should always receive the same results.
I can't guarantee this, but try it....
Patrick J. Shoaf, Systems Engineer <mailto:[EMAIL PROTECTED]>[EMAIL PROTECTED]
Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or 888-638-6963 Fax: 724-489-4386
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
