SUM([ALL | DISTINCT [BY(col-list)]] expression [%FOREACH(col-list)] [%AFTERHAVING])
|ALL||Optional — Specifies that SUM return the sum of all values for expression. This is the default if no keyword is specified.|
|DISTINCT||Optional — A 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)||Optional — A column name or a comma-separated list of column names. See SELECT for further information on %FOREACH.|
|%AFTERHAVING||Optional — 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.
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.
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.
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.
SQL optimization of a SUM calculation can use a bitslice index, if this index is defined for the field.
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.
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