Thanks for your feedback!
Need to tell us more? Click here or use the Feedback button.

Is this page helpful?

Contents

Synopsis
Description
Arguments
Changes Made During the Current Transaction
Examples
See Also

SUM (SQL)

An aggregate function that returns the sum of the values of a specified column.

Synopsis

SUM([ ALL | DISTINCT [BY(col-list)] ] 
  expression 
  [ %FOREACH(col-list) ] [ %AFTERHAVING ])

Description

The SUM aggregate function returns the sum of the values of expression. Commonly, expression is the name of a field (or an expression containing one or more field names) in the multiple rows returned by a query.

SUM can be used in a SELECT query or subquery that references either a table or a view. SUM can appear in a SELECT list or HAVING clause alongside ordinary field values.

SUM cannot be used in a WHERE clause. SUM cannot be used in the ON clause of a JOIN, unless the SELECT is a subquery.

SUM, like all aggregate functions, can take an optional DISTINCT clause. SUM(DISTINCT col1) totals only those col1 field values that are distinct (unique). SUM(DISTINCT BY(col2) col1) totals only those col1 field values in records where the col2 values are distinct (unique). Note however that the distinct col2 values may include a single NULL as a distinct value.

Data Values

SUM returns data type INTEGER for an expression with data type INT, SMALLINT, or TINYINT. SUM returns data type BIGINT for an expression with data type BIGINT. SUM returns data type DOUBLE for an expression with data type DOUBLE. For all other numeric data types, SUM returns data type NUMERIC.

SUM returns a value with a precision of 18. The scale of the returned value is the same as the expression scale, with the following exception: if expression is a numeric value with data type VARCHAR or VARBINARY, the scale of the returned value is 8.

By default, aggregate functions use Logical (internal) data values, rather than Display values.

SUM is normally applied to a field or expression that has a numeric value. Because only minimal type checking is performed, it is possible (though rarely meaningful) to invoke it for nonnumeric fields. SUM evaluates nonnumeric values, including the empty string (''), as zero (0). If expression is data type VARCHAR, the return value to ODBC or JDBC is of data type DOUBLE.

NULL values in data fields are ignored when deriving a SUM aggregate function value. If no rows are returned by the query, or the data field value for all rows returned is NULL, SUM returns NULL.

Optimization

SQL optimization of a SUM calculation can use a bitslice index, if this index is defined for the field.

Arguments

ALL

An optional argument specifying that SUM return the sum of all values for expression. This is the default if no keyword is specified.

DISTINCT

An optional DISTINCT clause that specifies that SUM return the sum of the distinct (unique) values for expression. DISTINCT can specify a BY(col-list) subclause, where col-list can be a single field or a comma-separated list of fields.

expression

Any valid expression. Usually the name of a column that contains the data values to be summed.

%FOREACH(col-list)

An optional column name or a comma-separated list of column names. See SELECT for further information on %FOREACH.

%AFTERHAVING

An optional argument that applies the condition found in the HAVING clause.

SUM returns the same data type as expression, with the following exception: TINYINT, SMALLINT and INTEGER are all returned as data type INTEGER.

Note:

SUM can be specified as an aggregate function or as a window function. This reference page describes the use of SUM as an aggregate function. SUM as a window function is described in Overview of Window Functions.

Changes Made During the Current Transaction

Like all aggregate functions, SUM always returns the current state of the data, including uncommitted changes, regardless of the current transaction’s isolation level. For further details, refer to SET TRANSACTION and START TRANSACTION.

Examples

In the following examples a dollar sign ($) is concatenated to Salary amounts.

The following query returns the sum of the salaries of all employees in the Sample.Employee database:

SELECT '$' || SUM(Salary) AS Total_Payroll
     FROM Sample.Employee

The following query uses %AFTERHAVING to return the sum of all salaries and the sum of salaries over $80,000 for each state in which there is at least one person with a salary > $80,000:

SELECT Home_State,
       '$' || SUM(Salary) AS Total_Payroll,
       '$' || SUM(Salary %AFTERHAVING) AS Exec_Payroll
     FROM Sample.Employee
     GROUP BY Home_State
     HAVING Salary > 80000
     ORDER BY Home_State

The following query returns the sum and the average of the salaries for each job title in the Sample.Employee database:

SELECT Title,
       '$' || SUM(Salary) AS Total,
       '$' || AVG(Salary) AS Average
     FROM Sample.Employee
     GROUP BY Title
     ORDER BY Average

The following query shows SUM used with an arithmetic expression. For each job title in the Sample.Employee database it returns the sum of the current salaries and the sum of the salaries with a 10% increase in pay:

SELECT Title,
       '$' || SUM(Salary) AS BeforeRaises,
       '$' || SUM(Salary * 1.1) AS AfterRaises
     FROM Sample.Employee
     GROUP BY Title
     ORDER BY Title

The following query shows SUM used with a logical expression using the CASE statement. It counts all of the salaried employees, and uses SUM to count all of the salaried employees earning $90,000 or more.

SELECT COUNT(Salary) As AllPaid, 
       SUM(CASE WHEN (Salary >= 90000)
           THEN 1 ELSE 0 END) As TopPaid
       FROM Sample.Employee