SUM
Purpose
This function returns the sum of expr within a window or group of rows.
Syntax
sum::=
Usage Notes
- With DISTINCT, the function considers duplicate values of expr only once. It considers all occurrences with ALL. If the function contains neither ALL nor DISTINCT, the default is ALL.
- This function supports arguments of types numeric and interval.
Examples
Aggregate Function
Result
DEPARTMENT | SUM_SALARY |
ACCOUNTS | 350000 |
HR | 285000 |
Analytic Function
SELECT
id, department, hire_date, current_salary,
SUM(current_salary) OVER (PARTITION BY department ORDER BY hire_date) SUM_SALARY
FROM employee_table ORDER BY department, hire_date;
Result
ID | DEPARTMENT | HIRE_DATE | CURRENT_SALARY | SUM_SALARY |
2005 | ACCOUNTS | 2013-01-01 | 80000 | 80000 |
2003 | ACCOUNTS | 2015-07-01 | 80000 | 160000 |
2002 | ACCOUNTS | 2017-01-01 | 65000 | 295000 |
2004 | ACCOUNTS | 2017-01-01 | 70000 | 295000 |
2001 | ACCOUNTS | 2018-07-01 | 55000 | 350000 |
1003 | HR | 2014-01-01 | 90000 | 90000 |
1002 | HR | 2016-01-01 | 70000 | 230000 |
1004 | HR | 2016-01-01 | 70000 | 230000 |
1001 | HR | 2018-01-01 | 55000 | 285000 |