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