SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—523725-004

9-152

SUM Function

SUM is an aggregate function that returns the sum of a set of numbers.

ALL | DISTINCT

specifies whether duplicate values are included in the computation of the SUM of

the expression. The default option is ALL, which causes duplicate values to be

included. If you specify DISTINCT, duplicate values are eliminated before the SUM

function is applied.

expression

specifies a numeric or interval value expression that determines the values to sum.

The expression cannot contain an aggregate function or a subquery. The

DISTINCT clause specifies that the SUM function operates on distinct values from

the one-column table derived from the evaluation of expression. All nulls are

eliminated before the function is applied to the set of values. If the result table is

empty, SUM returns NULL.

See Expressions on page 6-39.

Considerations for SUM

Data Type and Scale of the Result

The data type of the result depends on the data type of the argument. If the argument

is an exact numeric type, the result is LARGEINT. If the argument is an approximate

numeric type, the result is DOUBLE PRECISION. If the argument is INTERVAL data

type, the result is INTERVAL with the same precision as the argument. The scale of

the result is the same as the scale of the argument. If the argument has no scale, the

result is truncated.

Operands of the Expression

The expression includes columns from the rows of the SELECT result table—but

cannot include an aggregate function. The valid expressions are:

SUM (SALARY)

SUM (SALARY * 1.1)

SUM (PARTCOST * QTY_ORDERED)

SUM ([ALL | DISTINCT] expression)