Description of sum.gif follows
Description of the illustration sum.gif

See Also:

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


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.

See Also:

Table 2-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.

See Also:

"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;

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
   FROM employees;

---------- --------------- ---------- ----------
       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