Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL SUM Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: SUM

Definition:
In Oracle PL/SQL, SUM is a built in function which can be used to sum or total the values of a column. It can be used for both aggregate and analytic functions. SUM takes a numeric input and returns numeric output as the sum of all values contained by the column.

Example Syntax:

Aggregate Usage:

SUM(expression)


Analytic Usage:

SUM([DISTINCT | ALL] expression)  OVER  (partition clause)


Example Usage:

Aggregate Use
The SQL query below displays total salary of all employees in each department.

SELECT DEPT, SUM(SAL) SUM_SAL
FROM EMPLOYEE
GROUP_BY DEPT
ORDER_BY DEPT;

DEPT SUM_SAL
---------- ----------
10 4800
20 14100
30 9400
40 6400
50 3200/



Analytic Use
The query above can be reframed in order to demonstrate the analytic behavior of the SUM function:

SELECT DISTINCT DEPT, SUM(SAL)  OVER (PARTITION BY DEPT ORDER_BY DEPT) SUM_SAL
FROM EMPLOYEE
ORDER_BY DEPT;

DEPT SUM_SAL
---------- ----------
10 4800
20 14100
30 9400
40 6400
50 3200



Related Links:

Related Code Snippets:
  • SUM - The function returns the sum, for each node in every argument node-set, of the result of con...
  • RMAN: Command Summary - Summary of RMAN commands that you can execute at the RMAN prompt, within a...
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 167 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?