The always happens. I’m dealling with a crappy database that stores numbers as text, so I use the cast function…
sum(cast(total as real))
but I sometimes get numbers like this…
7891.90000110865
where the figure of 7891.90 is correct. The actual data is never more than two decimal places in precision, but I always get these weird totals.
Should I be using something other than cast?
Weird. It seems a bit like floating point innacuracy, but that wouldn’t give you quite so many decimal places of garbage on the right side compared to the max precision of real.
In any event, can you round to 2 decimal places, either in SQL or in your client language?
I’m guessing you are on sql server, which has a few quirks I am not used to, but does the construct “sum(cast(total as real(10,2)))” work in it.
Where the (10,2) tells it you want ten digit field with two spaces after the decimal,
Doesn’t accept. But after a bit of goggling I’ve replaced real with float in the cast and the problem goes away.
But I’m still curious to know why it happens 
eta: In fact, to make it even more of a weirdness, not only is the data never more precise than two decimal places, but everything is in multiples of .10 so effectively just one decimal place of precision for the actual data. So with the data I’m giving I should only ever see a 10 or a 20 or a 30 (and so on) after the decimal place.
I think there’ are two anomalies that combine together to create this problem.
First, from: float and real (Transact-SQL) - SQL Server | Microsoft Learn
“Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.”
Second, In SQL Server the ISO synonym for REAL = is FLOAT(24), whereas the default for FLOAT without specifying a precision is FLOAT(53).
My speculation is that the extra precision of the default FLOAT is such that you don’t see the weird junk at the end.
Out of curiosity, when you say the data is stored as text; are you saying that the data really is a data type of TEXT? Or could you be saying that the data type is CHAR or VARCHAR?
I don’t think it matters, but I’d certainly check that out in addition to my previous post to see if it matters.
This is definitely a floating point precision problem.
As Enright3 mentioned above, the reason you’re not seeing the junk decimals when you use float instead of real is due to the higher precision used by the float data type.
However, you can’t still be 100% sure that the same thing won’t happen even with the change you made (using float); such is the world of floating points in computers.
If you absolutely, positively, must be sure that this doesn’t happen again, then you will have to use a different data type (e.g., decimal). But if you’re alright with your floating points being valid only 99.99999% of the time, you can just continue using float or real.
IOW, if you’re a bank dealing with amounts in people’s bank accounts, approximate values won’t cut it. But if you’re dealing with the number of seconds it took to complete a transaction, approximate values are just fine (usually).
char(10).
FYI I’m dealing with bets. The minimum multiple of a bet currently allowed is ten cents, so all data is a multiple of 0.10.
Other amounts can enter into the table (such as if someone withdraws the one dollar twelve cents they have in their account) but these are filtered out by the query.
Since you’re dealing with money, you need to be absolutely precise. Can you do basic arithmetic on the figures? A simple method would be to multiply by 100, take the integer, then divide by 100.
Even better might be to use BCD
I’m assuming a MSSQL server basis here, but it applies equally to Sybase ASE and some other databases:
Float and real are approximate datatypes and, if you’re thrashing through a large number of calculations, will tend to give you this kind of rounding error.
If you’re dealing with financial transactions I’d recommend the money datatype, which always floats at 4 decimal places, which should be sufficient for your needs.
If you need greater precision go with a tailored decimal type with a specified precision.
Definitely a floating-point precision error.
Take a look at the list of SQL Server data types here: Data types (Transact-SQL) - SQL Server | Microsoft Learn. You want to be using one of the “Exact numeric” type rather than the “approximate numeric” type, to stop this from happening. Either money or decimal would appear to be appropriate. Both float and real are approximate numerics.
Agreed…I am not sure where the idea came from to use Float or Real in the first place. This is what the decimal data type was made for.
Are you saying use the money data type when casting? or change the field to money?
I’d love to change the field to money, but it’s a massive table and I think I’ll live with it being char(10) for now. So far the ‘problem’ hasn’t been a problem.
Optimally, the field itself should have a data type of money (unless some rows contain non-monetary values).
However, if you don’t want to go that route, casting the text field as a money or smallmoney data type would solve your immediate problem, and ensure you never see it again. (Casting it as float doesn’t come with that guarantee.)