Adding floats does not yield a correct result

Question

Imagine the following situation. Amounts are added to a total in a loop:

float total = 0;
float[] amounts = new float[]{...};
foreach(float amount in amounts)
{
 total += amount;
}

total, as wel as all the amounts are written to a database. When I calculate SUM(amount) in SQL, it results in a value that differs from total. Moreover, when I do the same calculation in C#, but this time adding the amounts to a value of type double,

double total = 0;
//the rest of the code is the same as above

then total represents the correct value.

Could this be caused by the difference in precision between a float and a double?

Note that it depends on the values. Most of the results of this calculation are correct.

• John Faulkner: A variable of type float only has 7 digits of precision where as a variable of type double has 15 digits of precision.
• It'sNotALie.: Show us the floats and then we would be able to help...?
• bvgheluwe: @newStackExchangeInstance: the values are random (not as in "randomly generated", but they can be anything)

Answer 1

Could this be caused by the difference in precision between a float and a double?

Yes. It can also sometimes simply be because not all values can be represented in an IEEE floating point; in many scenarios where you expect "exact" sums (money, etc), it is usually better to use decimal. However, that shouldn't to be interpreted to mean that decimal is "exact" - simply that the way it rounds is more conducive to how we humans think of rounding.

• bvgheluwe: Is it correct to say that decimal is more exact because it uses even more bits (than double to represent a value?
• Jeppe Stig Nielsen: One thing that can be surprising about .NET's System.Decimal is that it sometimes uses 29 decimal digits, and sometimes 28. For this reason calculations like 800m/3m * 3m and 500m/3m * 3m which people expect are equivalent, are not similar (guess which one is an integer (with trailing zeroes)).
• Jeppe Stig Nielsen: @BartVG It's more precise because of that.
• Marc Gravell: @BartVG double can represent much larger and much smaller values; it has more range, but less precision; msdn.microsoft.com/en-us/library/364x0z75(v=vs.110).aspx vs msdn.microsoft.com/en-us/library/678hzkk9(v=vs.110).aspx

Answer 2

Yes. SQL Server's float size is variant but you likely have 8 byte floats in the database. When you use doubles ( 8 bytes ) in the C# code the results are the same, when you use floats ( 4 bytes ) they're different. double values within the range of a float will not be the same as the float values.

• bvgheluwe: I should mention that they are stored as real in SQL Server. But even then the sum (of real values in SQL) is more accurate than the sum of float values in C#.