Why DB2 returns inaccurate SUM result on double column?

Body

Question:   Why the last query  as below returns +6.60619999999999E+002 instead of +6.60620000000000E+002?
$ db2 "create table tmp_day_act( avge_amount double)"
$ db2 "insert into tmp_day_act values(-1020)"
$ db2 "insert into tmp_day_act values(-19053.67)"
$ db2 "insert into tmp_day_act values(29869.35)"
$ db2 "insert into tmp_day_act values(-258.62)"
$ db2 "insert into tmp_day_act values(-8876.44)"
$ db2 "select sum(avge_amount) from tmp_day_act"

1
------------------------
  +6.60619999999999E+002

Answer:

The result you gets is expected. Floating-point numbers are approximations of real numbers and are considered approximate numeric types. A double-precision floating-point number is a 64-bit approximation of a real number. The number can be zero or can range from -1.7976931348623158e+308 to -2.2250738585072014e-308, or from 2.2250738585072014e-308 to 1.7976931348623158e+308.
 
You could refer to following link to understand how floating-point numbers are stored and calculated: https://en.wikipedia.org/wiki/IEEE_754-1985

Note that, on some platform, you may get the 'expected' result +6.60620000000000E+002, all depends on the processor hardware instructions available or the platform compiler.

Attached a simple c program to demonstrate it is outside of DB2:

$ cat test.c

_{#include<math.h>}

_{int main(int argc, char *argv[])
{
  double d1 = -1020 ;
  double d2 = -19053.67 ;
  double d3 = 29869.35 ;
  double d4 = -258.62 ;
  double d5 = -8876.44 ;
  double d6 = d1+d2+d3+d4+d5;}

  _{printf("d1: %.14e \n", d1);
  printf("d2: %.14e \n", d2);
  printf("d3: %.14e \n", d3);
  printf("d4: %.14e \n", d4);
  printf("d5: %.14e \n", d5);
  printf("d6: %.14e \n", d6);
  return 0;
}}

$ xlC -g -q64  test.c -o test
$ ./test
d1: -1.02000000000000e+03
d2: -1.90536700000000e+04
d3: 2.98693500000000e+04
d4: -2.58620000000000e+02
d5: -8.87644000000000e+03
d6: 6.60619999999999e+02

For a high level of accuracy/precision, you should use DECFLOAT or decimal.

Actually IBM discourage use of REAL and DOUBLE for precise decimal point calculations:
http://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.wn.doc/doc/c0023230.html

If you insist on using double, then ROUND/QUANTIZE can be used as a 'workaround':

$ db2 "select QUANTIZE(sum(avge_amount),DECFLOAT(0.01)) from tmp_day_act"                                                            

1                                                                       
------------------------------------------
                                    660.62

  1 record(s) selected.

$ db2 "select ROUND(sum(avge_amount),2) from tmp_day_act"

1
------------------------
  +6.60620000000000E+002

  1 record(s) selected.