# Why DB2 returns inaccurate SUM result on double column?

## 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

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.

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

ibm13286755