# incorrect value from SUM Function

Has anyone seen this before?

When doing a simple sum on a table:

select sum(column)
from table
where column is not null


I'm receiving the data 1558.39999999999 However, investigation of the data shows that all data points only go out to two decimal places.

In this case it should be impossible to receive the number above.

using another query:

select x.id, x.value, SUM(x2.value)
from x
join x2 on x.id >= x2.id
group by x.id, x.value
order by x.id


I was able to find the specific record where this occurs. The values are:

id, x.value, SUM
id, 3,       1557.68
id, 0.08,    1557.75999999999


When I ask sql server to sum these values I get

select 1557.68 + 0.08
1557.76


Has anyone seen anything like this? Is there a bug with SUM in t SQL I'm not aware of?

-
That's not a bug. It's how float datatypes work, not only in SQL. If you want accuracy, use DECIMAL –  ypercube Mar 6 '14 at 14:47
@ypercube I don't understand. Why would a float data type all of a sudden cause sql to incorrectly add the values? –  Lumpy Mar 6 '14 at 14:49
@ypercube - Thanks. I'll read up and correct my ignorance. –  Lumpy Mar 6 '14 at 14:55
Check the Fiddle as well. When SQL-Server (or any DBMS) read a literal (like 1557.68) it has to decide which datatype to use. It seems that in one case, SQL-Server decides to use a more accurate datatype, while in the SELECT SUM() query, it uses the underlying datatype of the column (is it REAL?) –  ypercube Mar 6 '14 at 15:01