SUM

SUM is valid only on columns that contain numeric values.

The data type of the result of the SUM always allows nulls, even if the operand of these functions is NOT NULL. Null values are not included in the calculation made by a built-in function.

The following example includes more than one column function in the SELECT statement. For Department 10 it calculates and displays the sum of employee salaries, the minimum, average, and maximum salary, and the number of employees (COUNT) in the department.

This query:

SELECT SUM(SALARY), MIN(SALARY), AVG(SALARY),
   MAX(SALARY), COUNT(*)
FROM Q.STAFF
WHERE DEPT = 10

Produces this report:

SUM(SALARY) MIN(SALARY)      AVG(SALARY) MAX(SALARY) COUNT(EXPRESSION)
----------- ----------- ---------------- ----------- -----------------
  83463.45     19260.25 20865.8625000000    22959.20                 4

You can write a column function like this:

SUM(expression)

The parentheses are required. expression is most often a column name, but can also be:

• An arithmetic expression containing at least one column name.
• DISTINCT, followed by a column name.

A column name in a function must not refer to a long string column or a column derived from a column function (a column of a view can be derived from a function). Column functions cannot be nested within other column functions.