- edited on
Working with DOUBLE values sometimes results in inaccuracy.
Those inaccuracies are not unexpected when dealing with DOUBLEs. As DOUBLE is an approximative data type, some values can not be stored exactly. This is a general issue of floating-point arithmetic.
You can verify the data type of your column by creating a table using the query and viewing the data types for the newly-created column, for example:
CREATE TABLE TEST AS SELECT ROUND(((71222-65504)/65504*100) ,1); -- Creates a DOUBLE
You can use an exact numeric type, like DECIMAL to remove inaccuracies. Such as the example below:
SELECT ROUND(CAST((71222-65504)/65504*100 AS DECIMAL(16,3)) ,1);
SELECT cast(1 as DECIMAL(17,16)) - cast(1E-16 as DECIMAL(17,16)) AS exact;
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.