Ratio_to_report tips Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Question:  I need assistance understanding the SQL ratio_to_report built-in function.  Can you give an example of ratio_to_report?

Answer:  Analytics can be used with aggregation using ratio_to_report. When combining analytics with aggregation, all expressions in the ORDER BY clause and column expressions have to be either part of the GROUP BY expressions or aggregate functions.

SELECT
   ROW_NUMBER() OVER (ORDER BY DEPTNO) "ROW_NUMBER",
   DEPTNO,
   SUM(SAL),
   TO_CHAR
   (
      100*RATIO_TO_REPORT
      (
         SUM(SAL)
      )
      OVER
      (),
      '990.00L',
      'NLS_CURRENCY=%'
   ) PCT
FROM
   EMP
GROUP BY
   DEPTNO;

ROW_NUMBER     DEPTNO   SUM(SAL)               PCT
---------- ---------- ---------- -----------------
         1         10       8750            30.15%
         2         20      10875            37.47%
         3         30       9400            32.39%


ROW_NUMBER is evaluated after the GROUP BY and ordered by the department number. RATIO_TO_REPORT returns the percentage of the total salary of the department to the overall total.

RATIO_TO_REPORT is used with ROLLUP, CUBE or GROUPING SETS, analytic functions can use GROUPING in the partition clause, and shown in this example:

SELECT
   ENAME,
   DEPTNO,
   JOB,
   SUM(SAL),
   TO_CHAR
   (
      100*RATIO_TO_REPORT(SUM(SAL))
        OVER (PARTITION BY GROUPING(ENAME),GROUPING(DEPTNO),JOB),
      '990.00L',
      'NLS_CURRENCY=%'
   ) PCT_JOB,  
   TO_CHAR
   (
      100*RATIO_TO_REPORT(SUM(SAL))
         OVER (PARTITION BY GROUPING(ENAME),GROUPING(JOB),DEPTNO),
      '990.00L',
      'NLS_CURRENCY=%'
   ) PCT_DEPTNO,  
   TO_CHAR
   (
      100*RATIO_TO_REPORT(SUM(SAL))
         OVER (PARTITION BY GROUPING(ENAME),GROUPING(DEPTNO),GROUPING(JOB)),
      '990.00L',
      'NLS_CURRENCY=%'
   ) PCT_EMP
FROM
   EMP
GROUP BY
   GROUPING SETS
   (
      (ENAME,DEPTNO,JOB),
      (DEPTNO,JOB),
      (DEPTNO),
      (JOB),
      ()
   )
ORDER BY
   GROUPING(ENAME),
   GROUPING(JOB),
   GROUPING(DEPTNO),
   DEPTNO,
   JOB,
   ENAME;

ENAME   DEPTNO JOB         SUM(SAL)      PCT_JOB   PCT_DEPTNO      PCT_EMP
------- ------ --------- ---------- ------------ ------------ ------------
MILLER      10 CLERK           1300       31.33%       14.86%        4.48%
CLARK       10 MANAGER         2450       29.61%       28.00%        8.44%
KING        10 PRESIDENT       5000      100.00%       57.14%       17.23%
FORD        20 ANALYST         3000       50.00%       27.59%       10.34%
SCOTT       20 ANALYST         3000       50.00%       27.59%       10.34%
ADAMS       20 CLERK           1100       26.51%       10.11%        3.79%
SMITH       20 CLERK            800       19.28%        7.36%        2.76%
JONES       20 MANAGER         2975       35.95%       27.36%       10.25%
JAMES       30 CLERK            950       22.89%       10.11%        3.27%
BLAKE       30 MANAGER         2850       34.44%       30.32%        9.82%
ALLEN       30 SALESMAN        1600       28.57%       17.02%        5.51%
MARTIN      30 SALESMAN        1250       22.32%       13.30%        4.31%
TURNER      30 SALESMAN        1500       26.79%       15.96%        5.17%
WARD        30 SALESMAN        1250       22.32%       13.30%        4.31%
            10 CLERK           1300       31.33%       14.86%        4.48%
            10 MANAGER         2450       29.61%       28.00%        8.44%
            10 PRESIDENT       5000      100.00%       57.14%       17.23%
            20 ANALYST         6000      100.00%       55.17%       20.67%
            20 CLERK           1900       45.78%       17.47%        6.55%
            20 MANAGER         2975       35.95%       27.36%       10.25%
            30 CLERK            950       22.89%       10.11%        3.27%
            30 MANAGER         2850       34.44%       30.32%        9.82%
            30 SALESMAN        5600      100.00%       59.57%       19.29%
               ANALYST         6000      100.00%       20.67%       20.67%
               CLERK           4150      100.00%       14.30%       14.30%
               MANAGER         8275      100.00%       28.51%       28.51%
               PRESIDENT       5000      100.00%       17.23%       17.23%
               SALESMAN        5600      100.00%       19.29%       19.29%
            10                 8750       30.15%      100.00%       30.15%
            20                10875       37.47%      100.00%       37.47%
            30                 9400       32.39%      100.00%       32.39%
                              29025      100.00%      100.00%      100.00%

The result set contains rows, subtotals and grand totals for each employee, department and job with the total salary.