Summation problems, question - 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 2005 Forums

Transact-SQL (2005)

Summation problems, question

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 11:24:40

Hi,

The following 2 queries return a different result...

Select SUM(x) - SUM(y) From NeedCoffee

Select SUM(x-y) From NeedMoreCoffee

Scary thing is the latter query code has existed on this report I am working on for a company. They have been using this for auditing purposes. Yikes! After a thourough manual check I feel the first query is accurate. Do you concur, sanity check? Here is some sample data.


x        y          group field
0	 913.07	    041604          
0	 37162.86   041604          
0	 4275.4	    041604          
0	 22464.44   041604          
44285	 0	    041604          
20530.77 0          041604

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-10-22 : 15:07:50

Works for me.


drop table #TMP
Select *
into #TMP
from
(
select convert(decimal(10,5),0) as x,	 convert(decimal(10,5),913.07)	as y  ,  041604 as mygroup Union all
select 0,	 37162.86,   041604           Union all
select 0,	 4275.4	,    041604           Union all
select 0,	 22464.44,   041604           Union all
select 44285,	 0,	    041604           Union all
select 20530.77, 0,          041604
) a

select mygroup,sum(x) -sum(y)
from #TMP
group by mygroup

select mygroup, sum(x - y)
from #TMP
group by mygroup

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 15:23:12

Thanks Vinnie . ok I found the problem , field is float. Just gonna have to use ROUND()

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources