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

Term: RATIO_TO_REPORT

Definition:
In Oracle PL/SQL, RATIO_TO_REPORT is an analytic function which returns the proportion of a value over the total set of values. A statement "RATIO_TO_REPORT of 2 over (1,2,3,4,5)" is (2/15) i.e. 0.133. Note that it returns NULL for NULL values of a column.

Example Syntax

RATIO_TO_REPORT (expression) (PARTITION BY <expression>)


The set of values is determined by the PARTITION BY <expression> clause. If that clause is omitted then the RATIO_TO_REPORT is computed over all rows returned by the query. Note that the ORDER_BY clause is not required.

Example Usage

The SQL query below calculates the ratio of an employee's salary over the sum of salaries in his department.

SELECT DEPT, EMPNO, SAL, RATIO_TO_REPORT(SAL) OVER (PARTITION BY DEPT) RATIO
FROM EMPLOYEE

DEPT EMPNO SAL RATIO
---------- ---------- ---------- ----------
10 100 2300 .479166667
10 110 2500 .520833333
20 120 5400 .382978723
20 140 3400 .241134752
20 170 5300 .375886525
30 180 7300 .776595745
30 130 2100 .223404255
40 150 6400 1
50 160 3200 1

9 rows selected.



Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 68 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?