why are my sums wrong? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

why are my sums wrong?

Author

Topic

mchilders
Starting Member

5 Posts

Posted - 2006-04-26 : 10:23:26

I am trying to convert data from an old database to a redesigned database. Here is a portion of script I'm using to do this. I'm copying a sum of a monetary field and placing it in another table. The problem is, when I compare the old data values with the new ones, a lot of the new ones are just wrong, and I can't figure out why. The new total is larger than the old total. Is there something in my code that is causing duplicate rows? I've attatched an Excel file with the results of the queries printed below.
Please help, thanks.

Here is the portion of the script I'm using to test the values:

/* simplified insert for testing purposes */ drop table icc_localsubtest

Select distinct
account as accountnum,
sum(total) as totalcharge
into icc_localsubtest
from convertlocal
group by account

/* end simplified insert */

/* test to see if values are copied faithfully. If not, reevaluate how the values are copied to icc_localsub */

drop table #totalcheck

select s.accountnum as NewAccount, l.account as Original,
sum(s.totalcharge) as newtotal, sum(l.total) as oldtotal into #totalcheck from icc_localsubtest s full outer join convertlocal l on s.accountnum=l.account group by s.accountnum, l.account

select * from #totalcheck
where newtotal<>oldtotal

/* end test */

Thanks

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-04-26 : 11:11:25

First of all, icc_localsubtest.totalcharge is the SUM of convertlocal.total, but in your comparisson query you're summing both again, this really doesn't make sense... Also, the fact that you're using a full outer join and summing could be another part of your problem. Change your compare query to this and see what you get:


SELECT * INTO #totalcheck 
FROM
   (SELECT accountnum as NewAccount, totalcharge as NewTotal 
    FROM icc_localsubtest) s
 FULL OUTER JOIN
   (SELECT account as Original, sum(total) as OldTotal
    FROM convertlocal
    GROUP BY account) l
 ON s.NewAccount = l.Original

~Travis

mchilders
Starting Member

5 Posts

Posted - 2006-04-26 : 11:21:08

Yes, that does make a difference.
BTW, I was using a full outer join so that if there were any accounts in one table but not in the other, they would show in the results. Your query accomplishes that better than my old query too though.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources