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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > "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. > ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
