Обсуждение: weird sum() results

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()?

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()?

Sounds just like "integer overflow".  Verify that your sum is not
exceeding the maximum values allowed for the type of 'amount' noted at

    http://www.postgresql.org/docs/postgres/datatype.htm#AEN840

If it is, try casting it to a larger capacity type (example below)...

Cheers,
Ed Loehr


CREATE TABLE foo (id SERIAL, i INT4);
] NOTICE:  CREATE TABLE will create implicit sequence 'foo_id_seq' for
SERIAL column 'foo.id'
] NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'foo_id_key'
for table 'foo'
CREATE
INSERT INTO foo (i) VALUES (2147483646);
INSERT 623743 1
INSERT INTO foo (i) VALUES (2147483646);
INSERT 623744 1
SELECT * FROM foo;
id|         i
--+----------
 1|2147483646
 2|2147483646
(2 rows)

SELECT * FROM foo WHERE i < 0;
id|i
--+-
(0 rows)

SELECT SUM(i) FROM foo;
sum
---
 -4
(1 row)

SELECT SUM(i::float8) FROM foo;
       sum
----------
4294967292
(1 row)

EOF

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@amgen.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)

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@amgen.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)
>
> ************