SUM

Finds the total of all values in the argument. Null values are ignored. SUM is an aggregate function. SUM can also be an aggregate analytic function. See "Aggregate Functions" for more details on aggregate functions. See "Analytic Functions" for more information on analytic functions.

SQL syntax

SUM ([ALL | DISTINCT] Expression) [OVER ([AnalyticClause])]

Parameters

SUM has the parameters:

Parameter Description

Expression

Can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.

ALL

Includes any duplicate rows in the argument of an aggregate function. If neither ALL nor DISTINCT is specified, ALL is assumed.

DISTINCT

Eliminates duplicate column values from the argument of an aggregate function.

OVER ([AnalyticClause])

If specified, indicates aggregate analytic function. See "Analytic Functions" for more information on analytic functions.

Description

  • If the SUM function is computed over an empty table in which GROUP BY is not used, SUM returns NULL.

  • If the SUM function is computed over an empty group or an empty grouped table (GROUP BY is used), SUM returns nothing.

  • If the source is TT_TINYINT, TT_SMALLINT, or TT_INTEGER, the result data type is TT_BIGINT.

  • If the source is NUMBER, then the result data type is NUMBER with undefined scale and precision.

  • For all other data types, the result data type is the same as the source.

  • If you do not use the AnalyticClause in your query, then SUM acts as an aggregate function.

  • If you specify DISTINCT and the AnalyticClause, then you can only specify the QueryPartitionClause. The OrderByClause and WindowingClause are not allowed.

Examples

Sum all employee salaries:

Command> SELECT SUM(salary) Total FROM employees;
 
TOTAL
< 691400 >
1 row found.