From: | Ed Loehr <eloehr(at)austin(dot)rr(dot)com> |
---|---|
To: | Hitesh Patel <hitesh(at)presys(dot)com> |
Cc: | "pgsql-general(at)postgreSQL(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] weird sum() results |
Date: | 2000-01-20 05:02:24 |
Message-ID: | 388696E0.FE4B3D7B@austin.rr.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Albert Loo | 2000-01-20 05:36:13 | Re: [GENERAL] jdbc question |
Previous Message | Jeff MacDonald <jeff@pgsql.com> | 2000-01-20 03:19:40 | parser |