Sum function returns incorrect value

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

General SQL Server Forums

Data Corruption Issues

Sum function returns incorrect value

Author

Topic

support.nicole
Starting Member

1 Post

Posted - 2011-06-24 : 03:25:17

Dear all,
Why in this special circumstances returns Sum function incorrect value? (tested on Microsoft SQL Server)

Sum two numbers, one negative, one positive, both differs with numbers after decimal separator and are same before decimal separator.
Example -9 and 9,07 or 13,1 and -13,2 etc.

Here is the code for test, as sum you wil get 0,070000000000000284 and I expect 0,07 :

CREATE DATABASE TestDB

CREATE TABLE TestTBL (memo varchar,calc float)

INSERT INTO TestTBL VALUES ('x', -9.00)
INSERT INTO TestTBL VALUES ('x', 9.07)

SELECT memo, SUM(calc) as Why
FROM TestTBL
GROUP BY memo

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-24 : 03:46:03

http://msdn.microsoft.com/en-us/library/ms187912.aspx
Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources