Caché SQL Reference
An aggregate function that returns the sum of the values of a specified column.
SUM([ALL | DISTINCT [BY(col-list)]] expression [%FOREACH(col-list)] [%AFTERHAVING])
||Optional Specifies that SUM return the sum of all values for expression. This is the default if no keyword is specified.
||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.
||Any valid expression. Usually the name of a column that contains the data values to be summed.
||Optional A column name or a comma-separated list of column names. See SELECT for further information on %FOREACH.
||Optional Applies the condition found in the HAVING clause.
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.
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.
cannot be used in a WHERE
cannot be used in the ON
clause of a JOIN
, unless the SELECT
is a subquery.
, 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.
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.
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.
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
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
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:
'$' || SUM(Salary) AS Total_Payroll,
'$' || SUM(Salary %AFTERHAVING) AS Exec_Payroll
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:
'$' || SUM(Salary) AS Total,
'$' || AVG(Salary) AS Average
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:
'$' || SUM(Salary) AS BeforeRaises,
'$' || SUM(Salary * 1.1) AS AfterRaises
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