Char to Numeric Convert Issue -- more decimal shown

[DolphinLei]

Hi All,
In project we are doing a sum calculation, before that we should convert this kind of char data '12,32' to '12.32', and then we want to get sum of '12.32' kind of data. our logic is here:
sum(REPLACE(Column_A, ',', '.')) as SUM_COA

However, it is strange that, the sum result is like that: 847667.639999999, actually after using replace function, every of our data will be a char, which only has 2 decimal, Can some one help to tell me why there are so much decimal in the sum result.

PS, after round the result 847667.639999999, it will be changed to 847667.64, and this is the correct result we want.

Thanks!

[JimKnicely]

Hi,

The SUM aggregate function returns a returns a DOUBLE PRECISION value for a floating-point expression. Otherwise, the return value is the same as the expression data type.

I'm not seeing the issue you are. In the following example, the result only has two decimal places:

Code: Select all

dbadmin=> select * from test;
   col
---------
 12,32
 10,14
 1231,22
(3 rows)

dbadmin=> select sum(replace(col, ',', '.')) from test;
   sum
---------
 1253.68
(1 row)

Maybe you can try explicitly converting the string to a NUMERIC having only two decimal places?

Code: Select all

dbadmin=> select sum(replace(col, ',', '.')::numeric(25,2)) from test;
   sum
---------
 1253.68
(1 row)

[id10t]

Hi!


Its a issue of representation of floating points in computers - binary vs decimal representation (nothing to Vertica).

Its a golden rule:
NEVER, but NEVER dont use floating-point numbers if precise computation is required

[DolphinLei]

Hi Knicely,
it is interesting that, in our porject,there are 10000+ records in the calculated table, when I sum part of them with: sum(REPLACE(Column_A, ',', '.')) as SUM_COA, for example 500 rows, it displays two decimal, but when sum of it for all 10000+records., it will show : 847667.639999999. I am just curious about the reason and want to know why.

[JimKnicely]

Hi,

Read the information discussed here http://docs.python.org/2/tutorial/floatingpoint.html

Maybe it'll help you understand why you should NOT be using floats

If I were you I'd switch to the NUMERIC data type!

[becky]

Hmm. This is a very interesting thread.

Not to add to the confusion, but can someone explain the following phenomenon?

Code: Select all

dbadmin=> CREATE TABLE t (c FLOAT);
CREATE TABLE

dbadmin=> INSERT INTO t VALUES (123456.7899999999);
 OUTPUT
--------
      1
(1 row)

When I query the table, the value is rounded:

Code: Select all

dbadmin=> SELECT * FROM t;
     c
-----------
 123456.79
(1 row)

But what's really weird to me is why this query returns no rows:

Code: Select all

dbadmin=> SELECT * FROM t WHERE c = 123456.79;
 c
---
(0 rows)

Is it because Vertica is actually storing the original value (123456.7899999999) I inserted into the table?

[JimKnicely]

#Becky - Try querying for the value you inserted. You'll find your row

Code: Select all

dbadmin=> CREATE TABLE t (c FLOAT);
CREATE TABLE
dbadmin=> INSERT INTO t VALUES (123456.7899999999);
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM t;
     c
-----------
 123456.79
(1 row)

dbadmin=> SELECT * FROM t WHERE c = 123456.79;
 c
---
(0 rows)

dbadmin=> SELECT * FROM t WHERE c = 123456.7899999999;
     c
-----------
 123456.79
(1 row)