#### 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 7876 ADAMS CLERK 1100 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

## No comments:

Post a Comment