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

Copy 
SELECT 
    department, 
   SUM(current_salary) SUM_SALARY 
FROM employee_table GROUP BY department;
Result
DEPARTMENT SUM_SALARY
ACCOUNTS 350000
HR 285000

Analytic Function

Copy 
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