ABCCORP 500, 150, 350 (from table 1, row 1, join table 2, row 1) ABCCORP 500, 50, 300 (from table 1, row 1, join table 2, row 2) ABCCORP 500, 50, 250 (from table 1, row 1, join table 2, row 3) ABCCORP 300, NULL,NULL (from table 1, row 2, no match in table 2)
If you sum those up you'll see that the results MySQL is giving are correct. Unfortunately I don't think you can do what you want to do without subselects or using two queries: effectively, you want to sum a set of summations. Try using a query like:
CREATE TEMPORARY TABLE my_results
SELECT TABLE1.accountno,
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
ORDER BY TABLE1.accountno
and then do: SELECT accountno, sum(invtotal), sum(invtotal) - sum(balance) FROM my_results GROUP BY accountno;
I've not tested that, but it should do something like what you want.
Cheers, Chris
Shaun Callender wrote:
Hello,
I'm using mySQL 4.0 trying to solve what I think is a simple problem.
to calculate the balance by account # can this be done without the use of temporary tables ?
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
example TABLE 1 : ABCCORP, 1000, 500$ TABLE 1 : ABCCORP, 1001, 300$
TABLE 2 : ABCCORP, 1000, 150$, 2003-01-01 TABLE 2 : ABCCORP, 1000, 50$, 2003,02-01 TABLE 2 : ABCCORP, 1000, 50$, 2003,03-01
SQL STMT 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 result set will show which is wrong. ABCCORP,1800$,200$,1600$
It should be ABCCORP,800$,200$,300$
How can this be done correctly ?
-- Shaun Callender (RSA Software Inc.)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
