SUM function example

The following example shows the SUM function used as a window function. The query returns a result set that partitions the data by DepartmentID, and then provides a cumulative summary (Sum_Salary) of employees' salaries, starting with the employee who has been at the company the longest. The result set includes only those employees who reside in California, Utah, New York, or Arizona. The column Sum_Salary provides the cumulative total of employees' salaries.

SELECT DepartmentID, Surname, StartDate, Salary,
SUM( Salary ) OVER ( PARTITION BY DepartmentID
    ORDER BY StartDate
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) 
AS "Sum_Salary"
FROM Employees
WHERE State IN ( 'CA', 'UT', 'NY', 'AZ' ) 
   AND DepartmentID IN ( '100', '200' )
ORDER BY DepartmentID, StartDate;

The table that follows represents the result set from the query. The result set is partitioned by DepartmentID.

DepartmentID Surname StartDate Salary Sum_Salary
1 100 Whitney 1984-08-28 45700.00 45700.00
2 100 Cobb 1985-01-01 62000.00 107700.00
3 100 Shishov 1986-06-07 72995.00 180695.00
4 100 Driscoll 1986-07-01 48023.69 228718.69
5 100 Guevara 1986-10-14 42998.00 271716.69
6 100 Wang 1988-09-29 68400.00 340116.69
7 100 Soo 1990-07-31 39075.00 379191.69
8 100 Diaz 1990-08-19 54900.00 434091.69
9 200 Overbey 1987-02-19 39300.00 39300.00
10 200 Martel 1989-10-16 55700.00 95000.00
11 200 Savarino 1989-11-07 72300.00 167300.00
12 200 Clark 1990-07-21 45000.00 212300.00
13 200 Goggin 1990-08-05 37900.00 250200.00

For DepartmentID 100, the cumulative total of salaries from employees in California, Utah, New York, and Arizona is $434,091.69 and the cumulative total for employees in department 200 is $250,200.00.

For more information about the exact syntax of the SUM function, see SUM function [Aggregate].

 Computing deltas between adjacent rows
 Complex analytics
 Rewriting using a ranking function
 See also