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
      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