InterSystems SQL Reference
SUM


An aggregate function that returns the sum of the values of a specified column.
Synopsis
SUM([ALL  DISTINCT [BY(collist)]] expression [%FOREACH(collist)] [%AFTERHAVING])
SUM returns the same
data type as
expression, with the following exception: TINYINT, SMALLINT and INTEGER are all returned as data type INTEGER.
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