SUM aggregate function

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

Note: The result of the function can be affected by the enablement of the large_aggregation configuration parameter.
Read syntax diagramSkip visual syntax diagramSUM(ALLDISTINCTexpression)

The schema is SYSIBM.

expression
An expression that returns a value of any built-in numeric data type.
The data type of the result is the same as the data type of the input expression, with the following exceptions:
  • If the data type of the input expression is SMALLINT, the data type of the result is INTEGER.
  • If the data type of the input expression is single-precision floating point (REAL), the data type of the result is double-precision floating point (DOUBLE).
  • If the data type of the input expression is DECFLOAT(n), the data type of the result is DECFLOAT(34).
  • If the input expression is a DECIMAL value with precision p and scale s, the result is a DECIMAL with scale s and precision as follows:
    Table 1. Result precision of the SUM aggregate function with DECIMAL input
    DECIMAL arithmetic mode 1 p Result precision
    default n/a 31
    DEC15 <=15 15
    DEC15 >15 MIN(31,p+10)
    DEC31 n/a MIN(31,p+10)
    Note:
    1. These modes are determined by the dec_arithmetic configuration parameter.

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. When interpreting the DISTINCT clause for decimal floating-point values that are numerically equal, the number of significant digits in the value is not considered. For example, the decimal floating-point number 123.00 is not distinct from the decimal floating-point number 123. The representation of the number returned from the query will be any one of the representations encountered (for example, either 123.00 or 123).

The result can be null. 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.

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

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.