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]
