Re: strange sum behaviour

["codeWarrior" <gpatnude(at)hotmail(dot)com>]

As an alternative -- you could do an inline type cast....

SELECT SUM(amount)::numeric(10, 2) FROM acc_trans WHERE trans_id=19721 AND 
chart_id=10019;






"Karen Hill" <karen_hill22(at)yahoo(dot)com> wrote in message 
news:1156873638(dot)219228(dot)218710(at)74g2000cwt(dot)googlegroups(dot)com(dot)(dot)(dot)
>
> "Andrew Baerg" wrote:
>> Hi,
>>
>> I am getting strange results from the sum function as follows:
>>
>> corp=# select amount from acc_trans where trans_id=19721 and 
>> chart_id=10019;
>>  amount
>> ---------
>>     4.88
>>    117.1
>>  -121.98
>> (3 rows)
>>
>> corp=# select sum(amount) from acc_trans where trans_id=19721 and
>> chart_id=10019;
>>          sum
>> ----------------------
>>  -1.4210854715202e-14
>> (1 row)
>>
>>
>> amount is defined as double precision. I noticed that if I cast amount
>> as numeric, the sum comes out 0 as expected.
>>
>
> Double precision accorrding to the documentation is "8 byte
> variable-precision, inexact".  That means when you do the sum, rounding
> occurs.  You should use Numeric or Decimal as the datatype.  The money
> type is depreciated so don't use it if what you are summing is currency.
>