Sum function returns incorrect value – MS SQL – Fórum – Programujte.com

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

This is common problem for computer representation of floating point numbers (ie. float).
You can round it to less precision (or you can use DECIMAL - fixed point number)
By the way: this is "correct" value - deviation is extremely small -> 4e-15