Sunday, November 17, 2013

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

1 comment:

Barinder said...

Very Interesting work !!