Re: math error or rounding problem Money type

Gregory Stark wrote:
> "Mark Kirkwood" <markir(at)paradise(dot)net(dot)nz> writes:
>
>> IFAIK (dimly recalling numerical analysis courses at university) SUM and ROUND
>> can *never* be commuted. In general the recommended approach is to round as
>> late as possible and as few times are possible - so your 1st query is the
>> correct or best way to go.

Justin, isn't your problem related precisely to what Tom said?

Now, when you're casting to Money, you're doing a cast like that
original_type -> text -> money (that's from your trailbalance view). I
suspect the original_type is NUMERIC (and I think it's a very good type
to keep your monetary data in).
My guess: what happens is that you have numbers with more that 6
fractional digits in your original table, and they're kept as NUMERIC
values. If you round them to the 6th fractional digit *before* summing
them up, you can indeed get different results from what you'd get if
you'd rounded them *after* doign the sum.

Compare:

=# select round(0.0000004 + 0.0000004, 6) ;
round
----------
0.000001
(1 row)

=# select round(0.0000004, 6) + round(0.0000004) ;
?column?
----------
0.000000

Do you see what (could've) happened? The first query is computed like this:
round(0.0000004 + 0.0000004, 0) => round(0.0000008, 6) => 0.000001
whereas the second one is more like:
round(0.0000004, 6) + round(0.0000004, 6) => 0.000000 + 0.000000 => 0.000000

Fractional parts that have been thrown away by the rounding may, when
added up, become fractional parts that get significant when you're
calculating the rounded value of the sum.

So yes, probably the way to go is do *all* computations in NUMERIC and
only cast when you're about to generate a report or present the data to
the end user. Otherwise you risk losing some cents like that (and you
need to be aware that a cast to MONEY *is* in fact a truncation, and you
will not get mathematically correct results).

Cheers,
Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin