Re: [GENERAL] Re: weird sum() results

The accumulator of SUM has the same type of the given argument and it may result in an overflow;
this kind of behavior is identical also for AVG function.
In the case of a SMALLINT (int2) or an INTEGER (int4) the overflow is silent.
In the case of a FLOAT PostgreSQL gives an error message like:
ERROR: Bad float8 input format -- overflow

To avoid undesired silent overflows you should use float8 function as in:
SUM ( float8 (argument) )

or SUM(int2*1.0)
SUM(int4*1.0)
SUM(int8*1.0)

Otherwise you may create your own SUM()/AVG() functions using int84pl as accumulator fot int4 and int42pl as
accumulator for int2. If you want an example I can send it to you.

José

Mark Dalphin wrote:

> On Wed, 19 Jan 2000 16:33:06, Hitesh Patel wrote:
>
> > I have a table with a filed named 'amount' and all the values are
> >
> > positive values, but if i do this:
> >
> > select sum(amount) from table where state = 'CA';
> >
> > I get a negative number. Doing a
> >
> > select name, amount from table where state = 'CA' and amount < '0';
> >
> > Returns 0 rows. Any ideas as to why i'm getting a negative value for
> > the sum()?
> >
>
> If the sum() is larger than the largest possible integer, the values will "wrap around" into the negative
> integers. I don't know how many values you are adding, but, assuming you are storing your amount as an
> 'int4', your sum will wrap at about 2 billion (2,147,483,647).
>
> HTH,
> Mark
>
> --
> Mark Dalphin email: mdalphin(at)amgen(dot)com
> Mail Stop: 29-2-A phone: +1-805-447-4951 (work)
> One Amgen Center Drive +1-805-375-0680 (home)
> Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)
>
> ************