SUM

The SUM function returns the sum of a set of numbers.

numeric-expression: An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. If the argument is a character-string or graphic-string, it is cast to DECFLOAT(34) before evaluating the function.

The data type of the result is the same as the data type of the argument values except that the result is:

DECFLOAT(34) if the argument values are DECFLOAT(16).
A double-precision floating point if the argument values are single-precision floating point
A large integer if the argument values are small integers
A decimal with precision mp and scale s if the argument values are decimal or nonzero scale binary numbers with precision p and scale s.

For information about the values of p, s, and mp, see Decimal arithmetic in SQL.

The function is applied to the set of values derived from the argument values by the elimination of null values. If DISTINCT is specified, duplicate values are eliminated.

The order in which the values are added is undefined, but every intermediate result must be within the range of the result data type.

Notes

Results involving DECFLOAT special values: If the data type of the argument is decimal floating-point and a special value of sNaN or -sNaN, or both +Infinity and -Infinity are included in the aggregation, an error is signaled. Otherwise, if +NaN or -NaN is found, the result is +NaN or -NaN. If +Infinity or -Infinity is found, the result is +Infinity or -Infinity.

Example

Using the EMPLOYEE table, set the host variable JOB_BONUS (DECIMAL(9,2)) to the total bonus (BONUS) paid to clerks (JOB='CLERK').
```
   SELECT SUM(BONUS)
     INTO :JOB_BONUS
     FROM EMPLOYEE
     WHERE JOB = 'CLERK'
```
Results in JOB_BONUS being set to 4000.