sql server - SQL sum strange behaviour

Question

There is someone that could me explain why if I sum this in SQL management studio

| field |
---------
-41,07
-141,96
13,6
6
-13,6
-6
5,39
1,44
-6,83
41,07
141,96

with the simple the query

SELECT
      sum(
      field
      ) s
  FROM [table]

I get this result

|s|
---
-2,8421709430404E-14

question from:https://stackoverflow.com/questions/66066879/sql-sum-strange-behaviour

Answer 1

This is because your choice of data type. You have defined field as a float, and so you have stated you are happy with floating point values; which are not accurate figures. When you aggregate many floating point values, you are going to get a scientific notation value and are very likely to lose accurary. If you're not happy with that, don't use float.

As you can see, the below gives 0 for when the value is a accurate data type (numeric) and not when is using a floating point data type.

SELECT SUM(NotAFloat) AS SummedNotAFloat,
       SUM(CONVERT(float,NotAFloat)) AS SummedFloat
FROM(VALUES(-41.07),
           (-141.96),
           (13.6),
           (6),
           (-13.6),
           (-6),
           (5.39),
           (1.44),
           (-6.83),
           (41.07),
           (141.96))V(NotAFloat);