Re: SUM PROBLEM...

On Thu, 2002-05-09 at 12:48, Sonia Sanchez Diaz wrote:
>
> Hy!
>
> I have one money type field and I need to plus it, but when I run the
> query I get a negative answer!
>
> But I don't have negative numbers into this field...???
>
> What's up here??
>
> The query is:
>
> select sum(field) from table;
>
> and the answer is:
>
> field
> --------
> -$5,083,598.58


Sounds like you are getting an overflow.
http://www.postgresql.org/idocs/index.php?datatype-money.html
Type Name    Storage    Description    Range
money        4 bytes    Fixed-precision    -21474836.48 to +21474836.47

NB: Its use is deprecated

Try using numeric:
http://www.postgresql.org/idocs/index.php?datatype.html#DATATYPE-NUMERIC-DECIMAL

I am not sure how you can convert the date to numeric, as on my database
(7.2.1) you cannot cast from money to numeric, or float (float would be
a bad idea for storage of monetary data).

 select sum(m::numeric) from foo;
ERROR:  Cannot cast type 'money' to 'numeric'

Maybe someone else could chime in at this point. : )

--
David Stanaway