Обсуждение: sum(population) under Sybase
> Just to add in here...Oracle: > > SQL> select sum(population) from nations; > > SUM(POPULATION) > --------------- > 2771277000 > > SQL> Here is Sybase. Note, I declared the population field as int. 1> SELECT * FROM t1 2> go name population -------------------- ----------- RUSSIA 281170000 INDIA 766140000 CHINA 1072220000 JAPAN 129947000 CANADA 25610000 U.S.A. 242080000 MEXICO 81160000 BRAZIL 141450000 ARGENTINA 31500000 (9 rows affected) 1> select sum(population) from t1 2> go Arithmetic overflow occurred. But with population defined as numeric(20,0) 1> select * from t2 2> go name population -------------------- ----------------------- JAPAN 129947000 CANADA 25610000 U.S.A. 242080000 MEXICO 81160000 BRAZIL 141450000 ARGENTINA 31500000 INDIA 766140000 CHINA 1072220000 RUSSIA 281170000 (9 rows affected) 1> select sum(population) from t2 2> go ----------------------------------------- 2771277000 I sort of like this behavior. This way, a valid sum over fields of type X can always be stored in a field of type X. Ocie Mitchell
> Here is Sybase. Note, I declared the population field as int. > 1> select sum(population) from t1 > 2> go > Arithmetic overflow occurred. > > But with population defined as numeric(20,0) > 1> select * from t2 > 2> go > name population > -------------------- ----------------------- > JAPAN 129947000 > CANADA 25610000 > U.S.A. 242080000 > MEXICO 81160000 > BRAZIL 141450000 > ARGENTINA 31500000 > INDIA 766140000 > CHINA 1072220000 > RUSSIA 281170000 > > (9 rows affected) > 1> select sum(population) from t2 > 2> go > > ----------------------------------------- > 2771277000 > > I sort of like this behavior. This way, a valid sum over fields of > type X can always be stored in a field of type X. How is that? numeric(20,0) guarantees that at least 20 digits can be stored. However, the SQL92 standard allows but does not require that more than 20 digits are handled. So the standard does not preclude overflow problems. In fact, many implementations will allocate a fixed amount of storage for the numeric field, so would have trouble coping with summation overflows. - Tom