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
