# SUM

Syntax

Description of the illustration sum.gif

"Analytic Functions" for information on syntax, semantics, and restrictions

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 data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.

Table 3-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

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.

"About SQL Expressions" for information on valid forms of `expr` and "Aggregate Functions"

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
ORDER BY manager_id, last_name, salary, l_csum;

MANAGER_ID LAST_NAME           SALARY     L_CSUM
---------- --------------- ---------- ----------
MANAGER_ID LAST_NAME                     SALARY     L_CSUM
---------- ------------------------- ---------- ----------
100 Cambrault                      11000      68900
100 De Haan                        17000     155400
100 Errazuriz                      12000      80900
100 Fripp                           8200      36400
100 Hartstein                      13000      93900
100 Kaufling                        7900      20200
100 Kochhar                        17000     155400
100 Mourgos                         5800       5800
100 Partners                       13500     107400
100 Raphaely                       11000      68900
100 Russell                        14000     121400
. . .
149 Hutton                          8800      39000
149 Johnson                         6200       6200
149 Livingston                      8400      21600
149 Taylor                          8600      30200
201 Fay                             6000       6000
205 Gietz                           8300       8300
King                           24000      24000
```