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.