Using the Ratio_to_report function - A technique to add sub-totals to the result set
By John Adolph Palinski
In a previous column I discussed using Oracle's analytic function, Ratio_to_report, to compute a ratio for each value in a sub-set of records. When using this function you will receive a result set similar to the one shown below. The Ratio_to_report function is used to compute an employee salary ratio. The ratio is each employee's share of the total department's salary.
SQL> select deptno department, ename name,
2 sal salary,
3 ratio_to_report(sal)
4 over (partition by deptno) ratio
5 from emp;
DEPARTMENT NAME SALARY RATIO
----------------- ----------- ----------- -------------
10 CLARK 2450 .28
10 KING 5000 .571428571
10 MILLER 1300 .148571429
20 SMITH 800 .073563218
20 ADAMS 1100 .101149425
20 FORD 3000 .275862069
20 SCOTT 3000 .275862069
20 JONES 2975 .273563218
30 ALLEN 1600 .170212766
30 BLAKE 2850 .303191489
30 MARTIN 1250 .13297872
30 JAMES 950 .10106383
30 TURNER 1500 .159574468
30 WARD 1250 .132978723
14 rows selected.
SQL>
It is not readily evident that the ratio is based upon each individual department's total salaries. You can only determine that the ratio is based upon the department by looking at the ratios. View the first three ratios for Department 10: .28, .57, and .14. The sum of these ratios is 99 indicating that the ratio is based upon the total department salaries.
A second problem with the result set is that the viewer cannot determine the magnitude of the ratio.value. For example, the first row has a ratio of 28%. Rows six and seven have ratios of 27%. Does this indicate that the employee salaries for the three rows is somewhat equal. No it does not. Reviewing the rows you can see that there is a $550 difference between row one and the other three rows. This can be a little misleading if the reader is not careful.
The result set would be more readable if a break with the total department salary can be added between each department's records. This break is really a sub-total displaying the ratio calculation denominator, the total department salaries. Most report writing tools can add this sub-total. It can also be done using the Select statement that creates the result set. Add a unioned Select statement to the original Select statement. The second statement is used to compute the total department salaries. This was done in the following result set. Notice how much more readable this result set is.
SQL> select department, name,
2 salary, ratio
3 from
4 (select deptno department, ename name,
5 sal salary, ratio_to_report(sal)
6 over (partition by deptno) ratio,
7 to_char(deptno) sort_column
8 from emp
9 union
10 select null, 'Total Department '||deptno||' Salaries', sum(sal),
11 null, to_char(deptno)||'a'
12 from emp
13 group by deptno)
14 order by sort_column;
DEPARTMENT NAME SALARY RATIO
----------------- ---------------------------------- ---------- ------------
10 CLARK 2450 .28
10 KING 5000 .571428571
10 MILLER 1300 .148571429
Total Department 10 Salaries 8750
20 ADAMS 1100 .101149425
20 FORD 3000 .275862069
20 JONES 2975 .273563218
20 SCOTT 3000 .275862069
20 SMITH 800 .073563218
Total Department 20 Salaries 10875
30 ALLEN 1600 .170212766
30 BLAKE 2850 .303191489
30 JAMES 950 .10106383
30 MARTIN 1250 .132978723
30 TURNER 1500 .159574468
30 WARD 1250 .132978723
Total Department 30 Salaries 9400
17 rows selected.
SQL>
The difficult part in creating the above result set was to get the sub-totals to sort in the proper location. Without special techniques the sub-total rows would not have appeared at the end of the department's records. They would have been sorted according to the department's name values and been interspersed within the department's records. For example, the last row with the Name value "Total Department 30 Salaries" would have appeared between the MARTIN and TURNER records.
Proper sorting was caused by two techniques. The creation of an artificial sort value and an in-line view. The artificial sort order is the Sort_column expression. In the first Select statement this value consists of the Deptno value. In the second Select statement this value consists of the Deptno value and the letter 'a', causing this value to be sorted after the normal Deptno values. It is necessary have the Sort_column but it is not desireable to see the Sort_column values. This was achieved by wrapping the two unioned Select statements in an In-line view. An In-line view is a Select statement that resides in the From clause. It in essence creates a virtual result set when it is executed. The In-line allows me to sort the records based upon the Sort_column, but not display the column in the result set.
Adding the sub-total will enhance your use of the Ratio_to_report function.
John Adolph Palinski is a former adjunct faculty member at the University of Nebraska at Omaha and Iowa Western Community College. He is the author of the "Oracle Database Construction Kit", Que, 1997, the "Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts", Addison Wesley Longman, 2002, and "Oracle 9i Developer: Developing Web Applications With Forms Builder", Course Technology, 2003. He has also written numerous articles for periodicals such as Oracle Internals and Oracle Professional. He provides Oracle training. He may be contacted at www.oracle-trainer.com.
