SQL Reference

SUM

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

The schema is SYSIBM.

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

The argument values must be numbers (built-in types only) and their sum must be within the range of the data type of the result.

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

• The result is a large integer if the argument values are small integers.
• The result is double-precision floating point if the argument values are single-precision floating point.

If the data type of the argument values is decimal, the precision of the result is 31 and the scale is the same as the scale of the argument values. The result can be null.

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 also eliminated.

If the function is applied to an empty set, the result is a null value. Otherwise, the result is the sum of the values in the set.

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 2800 when using the sample table.