How to use RATIO_TO_REPORT with ROLLUP?

Hi there.

This seems like a stupid question, but I cannot figure out how to combine RATIO_TO_REPORT and ROLLUP.

A simple example is the following:

select deptno, ename, sum(sal),
  round(ratio_to_report(sum(sal)) over () * 100) as percentage from emp
group by rollup(deptno, ename)
order by deptno, ename;

which is clearly wrong, because the result is:

    DEPTNO ENAME SUM(SAL) PERCENTAGE ---------- ---------- ---------- ----------

        10 CLARK            2450          3
        10 KING             5000          6
        10 MILLER           1300          1
        10                  8750         10
        20 ADAMS            1100          1
        20 FORD             3000          3
        20 JONES            2975          3
        20 SCOTT            3000          3
        20 SMITH             800          1
        20                 10875         12
        30 ALLEN            1600          2
        30 BLAKE            2850          3
        30 JAMES             950          1
        30 MARTIN           1250          1
        30 TURNER           1500          2
        30 WARD             1250          1
        30                  9400         11
                           29025         33

The percentage for the last row should be 100, not 33, for row 4 it should be 30, for row 1 it should be 8, etc. I cannot figure out how to make the partitions for the OVER() clause to make this work. And presumably I would need some DECODE and GROUPING for various cases for that column.

Any advice?

TIA. Finn Received on Thu May 16 2002 - 16:31:16 CDT