Recipe14.12.Calculating Percent Relative to Total

Recipe 14.12. Calculating Percent Relative to Total

Problem

You want to report a set of numeric values, and you want to show each value as a percentage of the whole. For example, you are on an Oracle system and you want to return a result set that shows the breakdown of salaries by JOB so that you can determine which JOB position costs the company the most money. You also want to include the number of employees per JOB to prevent the results from being misleading. You want to produce the following report:

 JOB         NUM_EMPS PCT_OF_ALL_SALARIES --------- ---------- ------------------- CLERK              4                  14 ANALYST            2                  20 MANAGER            3                  28 SALESMAN           4                  19 PRESIDENT          1                  17 

As you can see, if the number of employees is not included in the report, it would look as if the president position takes very little of the overall salary. Seeing that there is only one president helps put into perspective what that 17% means.

Solution

Only Oracle enables a decent solution to this problem, which involves using the built-in function RATIO_TO_REPORT. To calculate percentages of the whole for other databases, you can use division as shown in "Determining the Percentage of a Total" in Chapter 7.

 1  select job,num_emps,sum(round(pct)) pct_of_all_salaries 2   from ( 3  select job, 4         count(*)over(partition by job) num_emps, 5         ratio_to_report(sal)over()*100 pct 6    from emp 7         ) 8   group by job,num_emps 

Discussion

The first step is to use the window function COUNT OVER to return the number of employees per JOB. Then use RATIO_TO_REPORT to return the percentage each salary counts against the total (the value is returned in decimal):

  select job,        count(*)over(partition by job) num_emps,        ratio_to_report(sal)over()*100 pct   from emp JOB         NUM_EMPS        PCT --------- ---------- ---------- ANALYST            2 10.3359173 ANALYST            2 10.3359173 CLERK              4 2.75624462 CLERK              4 3.78983635 CLERK              4  4.4788975 CLERK              4 3.27304048 MANAGER            3 10.2497847 MANAGER            3 8.44099914 MANAGER            3 9.81912145 PRESIDENT          1 17.2265289 SALESMAN           4 5.51248923 SALESMAN           4 4.30663221 SALESMAN           4 5.16795866 SALESMAN           4 4.30663221 

The last step is to use the aggregate function SUM to sum the values returned by RATIO_TO_REPORT. Be sure to group by JOB and NUM_EMPS. Multiply by 100 to return a whole number that represents a percentage (e.g., to return 25 rather than 0.25 for 25%):

  select job,num_emps,sum(round(pct)) pct_of_all_salaries   from ( select job,        count(*)over(partition by job) num_emps,        ratio_to_report(sal)over( )*100 pct   from emp        )  group by job,num_emps JOB         NUM_EMPS PCT_OF_ALL_SALARIES --------- ---------- ------------------- CLERK              4                  14 ANALYST            2                  20 MANAGER            3                  28 SALESMAN           4                  19 PRESIDENT          1                  17