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@xxxxxxxxx> wrote in message news:1156873638.219228.218710@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx > > "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. >
