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