Thread

1. sum(population) under Sybase

   ocie@paracel.com — 1998-02-11T20:01:50Z

   > 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
   
   
   
   

2. Re: [HACKERS] sum(population) under Sybase

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-12T02:44:55Z

   > 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