RATIO_TO_REPORT of Analytic Functions in Oracle SQL

RATIO_TO_REPORT of Analytic Functions in Oracle SQL

RATIO_TO_REPORT
RATIO_TO_REPORT function computes the ratio of a value to the sum of a set of values. If it null then result is null.
RATIO_TO_REPORT result is computed over all rows returned by the SQL query.

Syntax:

RATIO_TO_REPORT (expr) over ( partition)

Examples of RATIO_TO_REPORT Analytic function:
Note: if it found null value in-between data then it will return null

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
FROM employees
where department_id = 30;

LAST_NAME      SALARY         RR
---------- ---------- ----------
Raphaely        11000 .441767068
Khoo             3100 .124497992
Baida            2900 .116465863
Tobias           2800 .112449799
Himuro           2600 .104417671
Colmenares       2500 .100401606

Manually it is calculated as

select last_name, salary,salary/
(Select sum(salary) FROM employees where department_id = 30) "RR" from employees where department_id = 30;

LAST_NAME      SALARY         RR
---------- ---------- ----------
Raphaely        11000 .441767068
Khoo             3100 .124497992
Baida            2900 .116465863
Tobias           2800 .112449799
Himuro           2600 .104417671
Colmenares       2500 .100401606

Note: if it found null value in-between data then it will return null

update employees set salary=null where employee_id = 115;

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
FROM employees
where department_id = 30;

LAST_NAME      SALARY EMPLOYEE_ID         RR
---------- ---------- ----------- ----------
Raphaely        11000         114 .504587156
Khoo                          115
Baida            2900         116 .133027523
Tobias           2800         117 .128440367
Himuro           2600         118 .119266055
Colmenares       2500         119 .114678899

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.