Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
Purpose
SUM
returns the sum of values of expr
. You can use it as an aggregate or analytic function.
This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.
If you specify DISTINCT
, then you can specify only the query_partition_clause
of the analytic_clause
. The order_by_clause
and windowing_clause
are not allowed.
Aggregate Example
The following example calculates the sum of all salaries in the sample hr.employees
table:
SELECT SUM(salary) "Total" FROM employees; Total ---------- 691400
Analytic Example
The following example calculates, for each manager in the sample table hr.employees
, a cumulative total of salaries of employees who answer to that manager that are equal to or less than the current salary. You can see that Raphaely and Cambrault have the same cumulative total. This is because Raphaely and Cambrault have the identical salaries, so Oracle Database adds together their salary values and applies the same cumulative total to both rows.
SELECT manager_id, last_name, salary, SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csum FROM employees; MANAGER_ID LAST_NAME SALARY L_CSUM ---------- --------------- ---------- ---------- 100 Mourgos 5800 5800 100 Vollman 6500 12300 100 Kaufling 7900 20200 100 Weiss 8000 28200 100 Fripp 8200 36400 100 Zlotkey 10500 46900 100 Raphaely 11000 68900 100 Cambrault 11000 68900 100 Errazuriz 12000 80900 . . . 149 Taylor 8600 30200 149 Hutton 8800 39000 149 Abel 11000 50000 201 Fay 6000 6000 205 Gietz 8300 8300 King 24000 24000