Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jones_d
Yak Posting Veteran
61 Posts |
 Posted - 2005-01-19 : 15:32:35
|
| Hi,I have 2 databases (each SS2000) and I have loaded the exact same data into each. I have about 90 identical transactions and when I get the sum of the value of these transactions:On one database I get -9.3132257461547852E-10On the other database I get 1.3969838619232178E-9Although the difference is very small it is causing problems in an application using this database.Does anyone know why the difference might occur? The values of the transactions are exactly the same. Note I got the above figures using just the sum function in QA.Any ideas or suggestions appreciated. |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-19 : 18:46:04
|
Check the column properties size and precision |
 |
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2005-01-20 : 05:07:02
|
| Hi,Both columns have a float data type. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-20 : 06:18:22
|
| A float is an approximat data type so you should expect innacuracies.It's may be due to the order it is doing the calculation.Try checking the individual values to see if you can find differences==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2005-01-20 : 06:59:04
|
| Hi,I have checked the individual entries and they are identical.The problem is manifesting itself in a reporting tool, where we filter on all clients with a balance different than zero.When this query is run against one database, a particular client is excluded because his balance (sum of transactions) is 0However, in the other database the same client appears on the report because he has a balance of 0.00000000000004Looking at the individual transactions, none of them has more than 2 decimal places but when you sum them, up to 10 figures can appear after the decimal point.Is there any database setting controlling how the sum function works? |
|
|
|
jones_d
Yak Posting Veteran
61 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-20 : 13:03:58
|
| instead of sum(fld)trysum(convert(decimal(18,4),fld)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Adding in SQL Server