Technical Blog Post
Abstract
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.
UID
ibm13286755