Let Us Learn Oracle - Part 40 of N [ Ratio_To_Report function of Oracle ]

Purpose: It is a analytic function that computes the ratio of a value to the sum of a set of values

Formula:

Ratio_To_Report = (Individual Value)/(Sum of Set of Values)

Example

SQL> Select EmpName,Salary,Ratio_to_Report(SALARY) Over() As RatioReportExample
  2  From tblEmployee
  3  Where DeptId = 1;

EMPNAME                                                SALARY RATIOREPORTEXAMPLE
-------------------------------------------------- ---------- ------------------
Deepak Kumar Goyal                                       6000         .146305779
Amitav Mallik                                           16000         .390148744
Deepak Singh                                             6890         .168007803
Shashi Bhushan                                           5120         .124847598
Amitav Salonki                                           7000         .170690076

Now let us see how the values are calculated

Let us first find the sum of the salary of the Employees that belongs to Department = 1

SQL>  SElect Sum(SALARY)
  2   From tblEmployee
  3  Where DeptId = 1;

SUM(SALARY)
-----------
      41010

And we want to find the Ratio_To_Report for the first employee i.e. "Deepak Kumar Goyal".

So at this stage we have,

Individual Value = 6000 and Sum of Set of Values = 41010

So put the values in the formula

Ratio_To_Report = 6000/41010 = .146305779

Let us do it for the third employee "Deepak Singh"

Ratio_To_Report = 6890/41010 = .168007803

Hope we understand how it works.Thanks for reading