## Sunday, November 17, 2013

### Three Ways of Calculating Percentage of Quantity Using Oracle SQL

#### Problem

Similar to the problem mentioned in In the earlier post, Three Ways of Calculating Percentage of Counts Using Oracle SQL, we often need to calculate percentage based on the quantity such as revenue by product type, total expense by department, etc. In the following example, how do we calculate the percentage of salary expense by job title?
```SQL> select EMPNO, ENAME, JOB, SAL from emp;

EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
7839 KING       PRESIDENT       5000
7698 BLAKE      MANAGER         2850
7782 CLARK      MANAGER         2450
7566 JONES      MANAGER         2975
7788 SCOTT      ANALYST         3000
7902 FORD       ANALYST         3000
7369 SMITH      CLERK            800
7499 ALLEN      SALESMAN        1600
7521 WARD       SALESMAN        1250
7654 MARTIN     SALESMAN        1250
7844 TURNER     SALESMAN        1500
7900 JAMES      CLERK            950
7934 MILLER     CLERK           1300

14 rows selected.
```

#### Solution

In the same way as described in Three Ways of Calculating Percentage of Counts Using Oracle SQL, three approaches are described here to calculate percentage by quantity.

##### Approach 1.
We use Oracle analytic ratio_to_report function and pass the function sum(sal) to it. This is the simplest solution.
```SQL> select JOB, ratio_to_report(sum(SAL)) over() as percentage from emp group by job order by JOB;

JOB       PERCENTAGE
--------- ----------
ANALYST   .206718346
CLERK     .142980189
MANAGER   .285099053
PRESIDENT .172265289
SALESMAN  .192937123
```
##### Approach 2.
We calculate total salary first in a sub query.
```SQL> select JOB, sum(SAL)/(select sum(SAL) as total from emp) as percentage from emp group by job order by JOB;

JOB       PERCENTAGE
--------- ----------
ANALYST   .206718346
CLERK     .142980189
MANAGER   .285099053
PRESIDENT .172265289
SALESMAN  .192937123
```
##### Approach 3.
Again, we make use of new_value to generate SQLPLUS variable &total_sal.
```SQL> column total_sal new_value total_sal
SQL> select sum(SAL) as total_sal from emp;

TOTAL_SAL
----------
29025

SQL> select JOB, sum(SAL)/&total_sal as percentage from emp group by job order by JOB;
old   1: select JOB, sum(SAL)/&total_sal as percentage from emp group by job order by JOB
new   1: select JOB, sum(SAL)/     29025 as percentage from emp group by job order by JOB

JOB       PERCENTAGE
--------- ----------
ANALYST   .206718346
CLERK     .142980189
MANAGER   .285099053
PRESIDENT .172265289
SALESMAN  .192937123
```