Обсуждение: 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) > > ************