Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
That's not a bug. It's how float datatypes work, not only in SQL. If you want accuracy, use DECIMAL –  ypercube Mar 6 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 at 14:49
5  
    
@ypercube - Thanks. I'll read up and correct my ignorance. –  Lumpy Mar 6 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 at 15:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.