I have a column full of floats. All entered to 3 decimals or less (i.e. 1.234, 0.543 120.4).

I have run

UPDATE TABLE xxx
SET MyColumn = ROUND(MyColumn TO 3)

When I view the column in a grid (and EDBMgr) I see the 3 decimals.

When I run

SUM(MyColumn)

the total displays as 429.34199999

... for just 1 group of records ... implying that there is a random record somewhere which is not exactly a whole number.

--

I have heard vaguely about this behaviour generally in Windows, i.e. the inability for Windows to be precisely accurate with some FLOAT values ... but I have never seen it in EDB before.

The data has been imported from Excel via a CSV file ... but crucially the CSV only shows accurate numbers ... so I am really at a loss as to where the inaccuracy is creeping in!

--

This is a totally non-critical issue! My users will survive ... but I just thought I would post it to see what could be done about it.

Adam

Adam


What type do you have the column defined as?

If its FLOAT then

Approximate numeric types are used when you wish to store a numeric value in an approximate representation with a floating decimal point. Using approximate numeric types can cause rounding errors due to the fact that certain numbers such as 0.33 cannot be accurately represented using floating-point precision.

try

DECIMAL or NUMERIC

Exact numeric types are used when you wish to store a numeric value in its exact representation without accumulating rounding errors. Specifically, NUMERIC and DECIMAL types allow you to specify the scale so that any numeric values with a greater scale are automatically rounded to the specified scale using the bankers rounding algorithm, which simply says that any digits past the specified scale are rounded to the specified scale using the following logic:



Roy Lambert [Team Elevate]

Thanks very much Roy, I will redefine columns with different data-types when I need precision.

I am still confused why the inaccuracy persists even after I have run

UPDATE mytable
SET Mycolumn = ROUND(MyColumn to 3)

... I would have assumed that would remove the error.

Adam

>I am still confused why the inaccuracy persists even after I have run
>
>UPDATE mytable
>SET Mycolumn = ROUND(MyColumn to 3)
>
>.. I would have assumed that would remove the error.

Its a logically good assumption, unfortunately its wrong. The problem (as I understand it) stems from the way in which floating point numbers are stored and manipulated. You've told it to round (say) 22 / 7 to three decimals ie 3.142 but that's not necessarily how its stored it might be 3.141999999. Any arithmetical operation (probably any mathematical operation as well) works on the underlying data as stored. Its why comparisons on floating point numbers are generally best carried out with a range equating to approximately. I think Delphi actually has some dedicated comparison operators for this - not sure cos I don't use them.

Roy Lambert [Team Elevate]