7.22.6 SUM

(1) Specification format

general-set-function-SUM ::= {SUM([ALL] value-expression)|SUM(DISTINCT value-expression)}

To Page Top

(2) Explanation of specification format

SUM([ALL] value-expression):

Determines the sum of the result of the value expression. For details about value expressions, see 7.20 Value expression.

ALL can be omitted. The results will be the same regardless of whether it is specified.

SUM(DISTINCT value-expression):

Determines the sum of the result of the value expression. For details about value expressions, see 7.20 Value expression.

Duplicate values are only counted once. For example, if the values in the result of the value expression are 2, 3, 2, and 5, the execution result will be 2 + 3 + 5 = 10.

To Page Top

(3) Rules

1. Null values are not included in the calculation.

2. In the following cases, the execution result will be a null value.

   • If the number of input rows is 0

   • If the values to be calculated are all null values

3. The following table shows the data type that can be specified in the value expression and the data type of the execution result of the general set function SUM.

   Table 7‒19: Data type that can be specified in the value expression and data type of the execution result of the general set function SUM

   No.	Data type that can be specified in the value expression	Data type of the execution result of general set function SUM
   1	INTEGER	INTEGER
   2	SMALLINT
   3	DECIMAL(m,n)	DECIMAL(38,n)
   4	DOUBLE PRECISION	DOUBLE PRECISION

4. If an overflow occurs during the computation of any set function, an overflow error is generated.

To Page Top

(4) Example

Example

Using the data in the salary table (SALARYLIST), this example determines the sum of the employee salaries (SAL) in each section (SCODE).

SELECT "SCODE",SUM("SAL") AS "SUM-SAL"
    FROM "SALARYLIST"
        GROUP BY "SCODE"

Example of execution results

To Page Top