SUM

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


        .-ALL------.                       
>>-SUM(-+----------+-numeric-expression)-----------------------><
        '-DISTINCT-'

The schema is SYSIBM.

The argument values can be of any built-in numeric data type, and their sum must be within the range of the data type of the result.

Start of change The arguments can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34). End of change

The data type of the result is determined as follows:

DECFLOAT(34) if the argument is DECFLOAT(n).
Large integer if the argument is small integer.
Double precision floating-point if the argument is single precision floating-point.
Otherwise, the result is the same as the data type of the argument.

The result can be null.

If the data type of the argument values is decimal, the scale of the result is the same as the scale of the argument values, and the precision of the result depends on the precision of the argument values and the decimal precision option:

If the precision of the argument values is greater than 15 or the DEC31 option is in effect, the precision of the result is min(31,P+10), where P is the precision of the argument values.
Otherwise, the precision of the result is 15.

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

If the function is applied to an empty set, the result is the null value. Otherwise, the result is the sum of the values in the set. The order in which the summation is performed is undefined but every intermediate result must be within the range of the result data type.

Example: Set the large integer host variable INCOME to the total income from all sources (salaries, commissions, and bonuses) of the employees represented in the sample table DSN8A10.EMP. If DEC31 is not in effect, the resultant sum is DECIMAL(15,2) because all three columns are DECIMAL(9,2).

   EXEC SQL SELECT SUM(SALARY+COMM+BONUS)
     INTO :INCOME
     FROM DSN8A10.EMP;