> 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



Wed, 11 Feb 1998 12:01:50 -0800 (PST)
ocie@paracel.com
sum(population) under Sybase
> 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


Thu, 12 Feb 1998 02:44:55 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] sum(population) under Sybase