Shaun Callender <[EMAIL PROTECTED]> wrote: > > 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$
It's not correct, because 800-200<>300, isn't it? > > How can this be done correctly ? Here is what I got with your data: mysql> select table1.*, table2.* from table1 left join table2 on table2.invoice=table1.invoice; +---------+---------+-------+---------+---------+---------+------------+ | account | invoice | total | account | invoice | payment | pdate | +---------+---------+-------+---------+---------+---------+------------+ | ABCCORP | 1000 | 500 | ABCCORP | 1000 | 150 | 2003-06-27 | | ABCCORP | 1000 | 500 | ABCCORP | 1000 | 50 | 2003-06-27 | | ABCCORP | 1000 | 500 | ABCCORP | 1000 | 50 | 2003-06-27 | | ABCCORP | 1001 | 300 | NULL | NULL | NULL | NULL | +---------+---------+-------+---------+---------+---------+------------+ 4 rows in set (0.00 sec) If I add SUM() functions and GROUP BY clause to the SELECT statement I should get SUM(table1.total)=500+500+500+300=1800, SUM(table2.payment)=150+50+50=250, 1800-250=1550. mysql> select table1.account, sum(table1.total) as total, sum(table2.payment) as payment, sum(table1.total)-sum(table2.payment) as balance from table1 left join table2 on table2.invoice=table1.invoice group by table1.account order by table1.account; +---------+-------+---------+---------+ | account | total | payment | balance | +---------+-------+---------+---------+ | ABCCORP | 1800 | 250 | 1550 | +---------+-------+---------+---------+ 1 row in set (0.00 sec) It's correct. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
