Three Ways of Calculating Percentage of Counts Using Oracle SQL

Problem

We are often asked to calculate summary information concerning percentage of counts, such as the percentage of our customers living in each state, percentage of credit card transactions by Merchant Category Code, percentage of employees by job tile, etc. For example, how do we calculate the percentage of number of employees by job title for the following table?

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

Approach 1.

The simplest solution is to use Oracle analytic function ratio_to_report function with count(*) as the input parameter.

SQL> select JOB, ratio_to_report(count(*)) over() as percentage from emp    group by job order by JOB;

JOB       PERCENTAGE
--------- ----------
ANALYST   .142857143
CLERK     .285714286
MANAGER   .214285714
PRESIDENT .071428571
SALESMAN  .285714286

Approach 2.

If we do not use anlytic function ratio_to_report, we need to do it in two steps.

SQL> select JOB, count(*)/(select count(*) as total from emp) as percentage from emp   group by job order by JOB;

JOB       PERCENTAGE
--------- ----------
ANALYST   .142857143
CLERK     .285714286
MANAGER   .214285714
PRESIDENT .071428571
SALESMAN  .285714286

Approach 3.

We can take advantage of "new_value" feature to define a variable total_num, fill it with the actual total counts of employees and use it ( as &total_num) in calculating the percentage.

SQL> column total new_value total_num
SQL> select count(*) as total from emp;

     TOTAL
----------
        14

SQL> select JOB, count(*)/&total_num as percentage from emp   group by job order by JOB;
old   1: select JOB, count(*)/&total_num as percentage from emp group by job order by JOB
new   1: select JOB, count(*)/        14 as percentage from emp group by job order by JOB

JOB       PERCENTAGE
--------- ----------
ANALYST   .142857143
CLERK     .285714286
MANAGER   .214285714
PRESIDENT .071428571
SALESMAN  .285714286