The sum of the SQL Server SQL Server should be set to 1, but the filter says otherwise

Question

I have a table with a field data type set to float. the values ​​for a given set of records should give the sum 1, and the field in select returns 1, however, the HAVING clause indicates otherwise.

Here are the exact values ​​that I have in my table, and as you can see, this example does the same. Why is this amount more than 1? I am lost!

with example as ( SELECT 'Code' as Code, cast(0.462 as float) as perc UNION ALL SELECT 'Code' as Code, cast(0.116 as float) as perc UNION ALL SELECT 'Code' as Code, cast(0.181 as float) as perc UNION ALL SELECT 'Code' as Code, cast(0.053 as float) as perc UNION ALL SELECT 'Code' as Code, cast(0.188 as float) as perc ) SELECT Code, SUM(perc) FROM example GROUP BY Code HAVING SUM(perc) > 1 

Answer 1

I think you misunderstand the floats. I do not mean that patronizingly, most people misunderstand floats, unfortunately.

Floating-point numbers are able to “re-scale” themselves. The ability to store huge numbers or tiny digits in the same data type. This does not mean that they are "infinitely accurate." It just means that they have an extreme range that they can cover. However, they pay for such flexibility. They have a rounding error because they are binary approximations of decimal numbers.

If you want to know more about this, there are many on the Internet. At first it gets confused with people, but as soon as you realize that you will be better, safer and wiser.

So, for your case, if you want to keep the exact value, do not use the floating point data type. Instead, use a fixed point data type.

If you change the value from FLOAT to something like DECIMAL(9,4) , then you will not get rounding errors associated with floating point numbers.

You cannot save 0.12345 in it, but for this you need to specify DECIMAL(9,5) . But you will always be guaranteed that any number that you can store in it will be stored accurately, and not approximately.

Answer 2

Because FLOAT is not an exact numeric type, so there are floating point approximations. Try using DECIMAL or NUMERIC :

 with example as ( SELECT 'Code' as Code, cast(0.462 as NUMERIC(5,3)) as perc UNION ALL SELECT 'Code' as Code, cast(0.116 as NUMERIC(5,3)) as perc UNION ALL SELECT 'Code' as Code, cast(0.181 as NUMERIC(5,3)) as perc UNION ALL SELECT 'Code' as Code, cast(0.053 as NUMERIC(5,3)) as perc UNION ALL SELECT 'Code' as Code, cast(0.188 as NUMERIC(5,3)) as perc ) SELECT Code, SUM(perc) FROM example GROUP BY Code HAVING SUM(perc) > 1