Precision errors when aggregating data

[James Foreman]

I'm using DB2 UDB 8.1.2 on Suse linux. Try this:

CREATE TABLE testsums (cost_of_sale decimal(7,2), price decimal(9,
2));
INSERT INTO testsums VALUES (10000, 30000);
SELECT cost_of_sale/price AS division1 FROM testsums;
SELECT sum(cost_of_sale)/sum(price) AS division2 FROM testsums;

The first select gives 0.333333333333333333333333
The second gives 0.

But it gets worse; if you then try

INSERT INTO testsums VALUES (1000, 2000);

and attempt the same selects again, you get:

DIVISION1
---------------------------------
0.333333333333333333333333
0.500000000000000000000000

and

DIVISION2
---------------------------------
0.

Does this come as a surprise to anybody? I don't understand why it's
not maintaining the decimal(7,2) in the results. If we have large
sums then you might expect the total of cost_of_sale to exceed the
space in a decimal(7,2), but it isn't clear why it should produce in
the first division results with (I lose count, but I think) about 24
decimal places.

What can we do to rectify this?

[Knut Stolze]

James Foreman <ja***********@abcmail.co.uk> wrote:

I'm using DB2 UDB 8.1.2 on Suse linux. Try this:

CREATE TABLE testsums (cost_of_sale decimal(7,2), price decimal(9,
2));
INSERT INTO testsums VALUES (10000, 30000);
SELECT cost_of_sale/price AS division1 FROM testsums;
SELECT sum(cost_of_sale)/sum(price) AS division2 FROM testsums;

The first select gives 0.333333333333333333333333
The second gives 0.

But it gets worse; if you then try

INSERT INTO testsums VALUES (1000, 2000);

and attempt the same selects again, you get:

DIVISION1
---------------------------------
0.333333333333333333333333
0.500000000000000000000000

and

DIVISION2
---------------------------------
0.

Does this come as a surprise to anybody? I don't understand why it's
not maintaining the decimal(7,2) in the results. If we have large
sums then you might expect the total of cost_of_sale to exceed the
space in a decimal(7,2), but it isn't clear why it should produce in
the first division results with (I lose count, but I think) about 24
decimal places.

What can we do to rectify this?

When I do a describe on your statement, I see that the resulting data type
of the division is DECIMAL(31, 0). So there are no digits after the
decimal point. Given that, your example above acts as one would expect.

$ db2 "describe SELECT sum(cost_of_sale) / sum(price) AS division2 FROM
testsums"

sqltype sqllen
-------------------- ------
485 DECIMAL 31, 0
I can't reproduce the result "0.5" that you got with 1000 and 2000. What
exactly did you do there?

To address this, you might want to adjust your data types like this (or
something similar):

$ db2 "SELECT decimal(sum(cost_of_sale), 7, 2) / decimal(sum(price), 9, 2)
FROM testsums"

1
---------------------------------
0.333333333333333333333333

1 record(s) selected.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[James Foreman]

>

When I do a describe on your statement, I see that the resulting data type
of the division is DECIMAL(31, 0). So there are no digits after the
decimal point. Given that, your example above acts as one would expect.

$ db2 "describe SELECT sum(cost_of_sale) / sum(price) AS division2 FROM
testsums"

sqltype sqllen
-------------------- ------
485 DECIMAL 31, 0
I can't reproduce the result "0.5" that you got with 1000 and 2000. What
exactly did you do there?

To address this, you might want to adjust your data types like this (or
something similar):

$ db2 "SELECT decimal(sum(cost_of_sale), 7, 2) / decimal(sum(price), 9, 2)
FROM testsums"

1
---------------------------------
0.333333333333333333333333

1 record(s) selected.

Thanks Knut. That does fix the rounding error, but it does still
confuse me why the resulting data type is decimal (31, 0) - I'd have
expected DB2 to preserve the level of accuracy from the base data
type. (It will quickly become tiresome to do this every time we have
to carry out such a division!)

To get the "0.5" result I do
select cost_of_sale, price, decimal((cost_of_sale/price), 7, 2),
cost_of_sale/price
FROM testsums;

COST_OF_SALE PRICE 3 4
------------ ----------- --------- ---------------------------------
10000.00 30000.00 0.33 0.333333333333333333333333
1000.00 2000.00 0.50 0.500000000000000000000000

which now gives me the result I want in column 3; although with my
previous comment, I'm confused why I'd have to specify something I'd
expect to be the default behaviour - I may have to take this up with
IBM, unless there's a configuration change that will produce this for
me.

[Ian]

James Foreman wrote:

When I do a describe on your statement, I see that the resulting data type
of the division is DECIMAL(31, 0). So there are no digits after the
decimal point. Given that, your example above acts as one would expect.

$ db2 "describe SELECT sum(cost_of_sale) / sum(price) AS division2 FROM
testsums"

sqltype sqllen
-------------------- ------
485 DECIMAL 31, 0
I can't reproduce the result "0.5" that you got with 1000 and 2000. What
exactly did you do there?

To address this, you might want to adjust your data types like this (or
something similar):

$ db2 "SELECT decimal(sum(cost_of_sale), 7, 2) / decimal(sum(price), 9, 2)
FROM testsums"

1
---------------------------------
0.333333333333333333333333

1 record(s) selected.

Thanks Knut. That does fix the rounding error, but it does still
confuse me why the resulting data type is decimal (31, 0) - I'd have
expected DB2 to preserve the level of accuracy from the base data
type. (It will quickly become tiresome to do this every time we have
to carry out such a division!)

There are factors influencing this:

1) When you do a SUM on a decimal(p,s) column, your result will
always be DECIMAL(31,s).

This happens in order to avoid overflows in the datatype, and
also explains why Knut's suggestion of casting the sum as
decimal(7,2) happens to work with your example data.
2) The reason that you get a result of 0. is a function of how DB2
does decimal division.

From the manual (Messages Reference, SQL0419N):

The formula used internally to calculate the scale of the result
for decimal division is:

Scale of result = 31 - np + ns - ds

where np is the precision of the numerator, ns is the scale of
the numerator, and ds is the scale of the denominator.

Dividing 2 decimal(31,2) results yields a result that's decimal(31,0)
(as you experienced).
To solve this: The better solution is to cast your SUM() results into
the correct precision. Of course, this could be somewhat time consuming,
as you've found.

There is also another solution:

The database configuration parameter MIN_DEC_DIV_3 can be set to YES,
which ensures that all decimal division operations will have a minimum
scale of 3. This parameter is not shown in the output of 'get db cfg'.
If you set this, you'll get a result (from your original query) of
0.500.

There are some potential complications with doing this, so check the
manual for the caveats if you go this route.
Good luck,


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----