Why does the SQL SUM() function return a non-zero total when a group float values should add up to 0?

Question

I am facing this issue with the SQL SUM() function I have three values that have the sum equals to zero as you can see the values but with the SUM() function the answer is that strange values as shown.

select Qty
from Stock
where ProductCode = '5LINE-15-1-30RU' 
    

Output:

1. 49.72
2. -31.065
3. -18.655

Whereas

select sum(Qty)
from Stock
where ProductCode = '5LINE-15-1-30RU'

Output:

-3.5527136788005E-15

The result have to be 0 but I got this awkward value.

Column Qty is a float.

Answer 1

You are using a float datatype which stores values as approximations c.f.

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

For cases when you need precision, e.g. when you need decimal/int values you should use a decimal/int datatype. Its pretty rare that you would use a float. If you are unable to change the column datatype you can convert it before summing e.g.

DECLARE @Test TABLE (Qty FLOAT);

INSERT INTO @Test (Qty)
VALUES
(49.72),
(-31.065),
(-18.655);

SELECT SUM(Qty)
    , SUM(CONVERT(money,Qty))
FROM @Test;

Returns:

FloatSum	ConvertedSum
-3.5527136788005E-15	0.00