7.22.6 SUM
SUM determines the sum.
- Organization of this subsection
(1) Specification format
general-set-function-SUM ::= {SUM([ALL] value-expression)|SUM(DISTINCT value-expression)}
(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.
(3) Rules
-
Null values are not included in the calculation.
-
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
-
-
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
-
If an overflow occurs during the computation of any set function, an overflow error is generated.
(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