calculate the balance by account #
TABLE 1 contains ACCOUNT #, INVOICE #, INV TOTAL
TABLE 2 contains there can be multiple payment records associated with an invoice ACCOUNT #, INVOICE #, PAYMENT, PAYMENT DATE
select TABLE1.accountno, SUM(TABLE1.invtotal) as INVTOTAL, SUM(TABLE2.PAYMENT) as PAYMENT SUM(TABLE1.invtotal ) - SUM( TABLE2.PAYMENT) as BALANCE LEFT JOIN TABLE2 ON TABLE2.invoiceno = TABLE1.invoiceno GROUP BY TABLE1.accountno ORDER BY TABLE1.accountno
The problem is that you're summing up invtotal over the detail rows -- in effect, invtotal gets multiplied by the number of payments. If you wanted this on an invoice-by-invoice basis, you'd be able to solve this by simply not summing it up. Most SQL dialects won't allow you to specify a column that isn't GROUPed without an aggregate function. I believe that MySQL *will*, but I'd avoid doing so anyway because it's neither portable nor necessary. Instead, GROUP BY invtotal along with everything else. This won't hurt anything, since invtotal is already uniquely identified by accountno and invoiceno. This gives you:
select TABLE1.accountno, TABLE1.invoiceno,
TABLE1.invtotal as INVTOTAL,
SUM(TABLE2.PAYMENT) as PAYMENT
TABLE1.invtotal - SUM( TABLE2.PAYMENT) as BALANCE
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE2.invoiceno = TABLE1.invoiceno
GROUP BY TABLE1.accountno, TABLE1.invoiceno, TABLE1.invtotalIf you find grouping by invtotal distasteful, you can instead use AVG, MAX, or MIN on invtotal:
select TABLE1.accountno, TABLE1.invoiceno,
AVG(TABLE1.invtotal) as INVTOTAL,
SUM(TABLE2.PAYMENT) as PAYMENT
AVG(TABLE1.invtotal) - SUM( TABLE2.PAYMENT) as BALANCE
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE2.invoiceno = TABLE1.invoiceno
GROUP BY TABLE1.accountno, TABLE1.invoicenoBut I digress, since you want a single total for each account. That's because I don't have a good solution for your actual problem <g>. To extend this to get a single grand total per account, you have the following options:
1) Issue the above command and programatically compute summaries for each accountno. 2) Wait for Views to be added to MySQL. Define a View incorporating the above, and do an aggregate query against the view. 3) Use the temporary table you've been trying to avoid, storing the results of the above query. Issue an aggregate query against it, summarizing by accountno, and then drop the temporary table.
Bruce Feist
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
