Hi all, > -----Original Message----- > From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] > Sent: Friday, June 27, 2003 3:18 PM > To: [EMAIL PROTECTED] > Subject: Re: incorrect SUM() results > > > 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?
Moreover, 150+50+50 is 250 not 200. > > > > > 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. >From the SQL Cartesian point of view ;-) However it's unusable for the business, isn't it? > > > -- > 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 I took it as a personal challenge in my "trying to master the JOIN statements". Until now, all what I've got is however a "per account_no & per invoice" situation, like this: SELECT table1.accountno,table1.invoiceno,invtotal AS Invoiced,sum(payment) AS Paid, invtotal-sum(payment) AS Balance FROM table1 LEFT JOIN table2 USING (invoiceno) GROUP BY accountno,invoiceno; Which displays (for the supplied data): +-----------+-----------+----------+------+---------+ | accountno | invoiceno | Invoiced | Paid | Balance | +-----------+-----------+----------+------+---------+ | ABC | 1000 | 500 | 250 | 250 | | ABC | 1001 | 300 | 0 | 300 | +-----------+-----------+----------+------+---------+ And that's a good report for the business. I'm somehow pesimistic about doing the requested totals in only one statement, but I'm trying to do it without temporary tables at least... Bests, Lian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
