Purpose
SUM() returns the sum for the specified parameter. You can use this function as an aggregate or analytic function.
Note
- When you use this function 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 this function 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. This parameter is optional. Default value: ALL.
|
| expr | An expression of a numeric data type or a data type 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, see Analytic functions. |
Notice
If you specify the DISTINCT or UNIQUE keyword, you can only specify the analytic_clause. The order_by_clause and windowing_clause parameters are not allowed.
Return type
The return type is the same as the data type of 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
Example of an aggregate function
Obtain the sum of salary in the employees table.
obclient> SELECT SUM(salary) FROM employees;
+-------------+
| SUM(SALARY) |
+-------------+
| 70300 |
+-------------+
1 row in set
Example of an analytic function
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