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.
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].
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|