Re: [GENERAL] weird sum() results

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