Patrick, Thanks! But - same result... It was worth a shot, however I still got 12 instances of 'amend' instead of 1.
If I can't work it out, I'll just split up this one query into two and see about using PHP to do the math. Not something I wanted to do, but it should work just as well. Thanks again, Jason ----- Original Message ----- From: Patrick Shoaf <[EMAIL PROTECTED]> Date: Tuesday, August 19, 2003 12:07 pm Subject: Re: JOINs > At 03:18 PM 8/19/2003, you wrote: > >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.... > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
