Purpose
You can call this function to return the sum for the specified parameter. You can use it as an aggregate or analytic function.
Note
- When you use it as an analytic function, use the
OVERclause to define a window over the data on which the function operates. The function operates on a group of rows to return a list of values.- When you use it as an aggregate function, the function operates on a set of rows and returns a single value. You do not need to add the
OVERclause.
Syntax
SUM([ DISTINCT UNQIUE ALL ] expr) [ OVER (analytic_clause) ]
Parameters
| Parameter | Description |
|---|---|
| DISTINCT UNQIUE ALL | (Optional) Specifies whether deduplication is required in the calculation. Default value: ALL.
|
| expr | A numeric data type or an expression that can be implicitly converted to a numeric data type. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information about analytic functions, see Analytic functions. |
Notice
If you specify the
DISTINCTorUNIQUEkeyword, you can only specify theanalytic_clause. Theorder_by_clauseandwindowing_clauseparameters are not allowed.
Return types
This function returns the same value type as the expr parameter.
Examples
Assume that you have created the table employees.
obclient> SELECT * FROM employees;
+---------------+-----------+------------+--------+
DEPARTMENT_ID LAST_NAME HIREDATE SALARY
+---------------+-----------+------------+--------+
30 Raphaely 2017-07-01 1700
30 De Haan 2018-05-01 11000
40 Errazuriz 2017-07-21 1400
50 Hartstein 2019-10-05 14000
50 Raphaely 2017-07-22 1700
50 Weiss 2019-10-05 13500
90 Russell 2019-07-11 13000
90 Partners 2018-12-01 14000
+---------------+-----------+------------+--------+
8 rows in set
Aggregate example
Obtain the sum of salary in the employees table.
obclient> SELECT SUM(salary) FROM employees;
+-------------+
SUM(SALARY)
+-------------+
70300
+-------------+
1 row in set
Analytic example
Group the data records in the table by the department_id column and sort the data records by the salary column in ascending order. Return the sum of salary column.
obclient> SELECT department_id, last_name, salary,
SUM(salary) OVER
(PARTITION BY department_id ORDER BY salary RANGE UNBOUNDED PRECEDING) s_sum
FROM employees
ORDER BY department_id,s_sum;
+---------------+-----------+--------+-------+
DEPARTMENT_ID LAST_NAME SALARY S_SUM
+---------------+-----------+--------+-------+
30 Raphaely 1700 1700
30 De Haan 11000 12700
40 Errazuriz 1400 1400
50 Raphaely 1700 1700
50 Weiss 13500 15200
50 Hartstein 14000 29200
90 Russell 13000 13000
90 Partners 14000 27000
+---------------+-----------+--------+-------+
8 rows in set