wibbleian2 wrote:

> SQL> create table tmp (id integer, parent_id integer, qty double precision);
> SQL> insert into tmp values (1,1,86.85);
> SQL> insert into tmp values (2,1,-14.475);
> SQL> insert into tmp values (3,1,57.90);
> SQL> select sum(qty) from tmp;
>
> SUM
> =======================
>
> 130.2750000000000
>
> SQL> select cast(sum(qty) as decimal(9,2)) from tmp;
>
> CAST
> ============
>
> 130.28
>
> SQL> select parent_id, cast(sum(qty) as decimal(9,2)) from tmp group by
> parent_id;
>
> PARENT_ID CAST
> ============ ============
>
> 1 130.27

This appears to be the classical problem with inherently approximate
floating point types and rounding. Although double has a lot of
significant digits, it will still suffer from rounding errors on each
and every operation you apply, be it addition, subtraction or whatever.

The thing is, although 130.2750000000000 looks like a very exact number
in decimal presentation, double stores it in binary form, in other words
base 2. In that base, this number would look something like this:

10000010.010001100110011001100110...

Note that the tail repeats 0011 forever, just like 1/13 is
0,07692307692307692307692307692307... which repeats 076923 forever.

At the same time, double only stores N binary digits, where I guess N is
48 or something (not sure). In other words, the double datatype CANNOT
store 130.275 exactly. It suffers from rounbding errors.

Now, lets say 130.275 is stored as 130.275000000000000000001. Then your
rounding cast will round up to 130.28. But if the rounding error is
negative, e.g. it's stored as 130.274999999999999999999, your rounding
cast will produce 130.27.

Why the rounding errors differ between group and no group, I cannot say,
but it's not that strange. Maybe it just happens that the terms are
added in a different order, and so, the rounding error in each addition
is different. The net result is a small pos error in one case and a
small neg error in the other case.

What you should do is to cast to decimal BEFORE adding. Like this:

select sum(cast(qty as decimal(9,2))) from tmp;

and:

select parent_id, sum(cast(qty as decimal(9,2))) from tmp group by
parent_id;

Does it work?

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64