Oracle Database Discussions

this???

  1  SELECT empno, ename, sal, total, ROUND(((sal*100)/total), 2) percent
  2  FROM(
  3  SELECT empno, ename, sal, SUM(sal) OVER() total
  4* FROM emp)
16:56:42 SQL> /

     EMPNO ENAME             SAL      TOTAL    PERCENT
---------- ---------- ---------- ---------- ----------
      7369 SMITH             800      29025       2.76
      7499 ALLEN            1600      29025       5.51
      7521 WARD             1250      29025       4.31
      7566 JONES            2975      29025      10.25
      7654 MARTIN           1250      29025       4.31
      7698 BLAKE            2850      29025       9.82
      7782 CLARK            2450      29025       8.44
      7788 SCOTT            3000      29025      10.34
      7839 KING             5000      29025      17.23
      7844 TURNER           1500      29025       5.17
      7858 Tonny                      29025

     EMPNO ENAME             SAL      TOTAL    PERCENT
---------- ---------- ---------- ---------- ----------
      7876 ADAMS            1100      29025       3.79
      7900 JAMES             950      29025       3.27
      7902 FORD             3000      29025      10.34
      7934 MILLER           1300      29025       4.48

select emp_id,sum(sal) sal,sum(pct_sal) pct_sal
 from (
    select emp_id,sal,sal*100/sum(sal) over () pct_sal
    from your_table
 )
 group by rollup(emp_id)
;

SQL> create table mytable
  2  as
  3  select 101 emp_id, 10000 sal from dual union all
  4  select 102, 15000 from dual union all
  5  select 103, 20000 from dual
  6  /

Tabel is aangemaakt.

SQL> select emp_id
  2       , sal
  3       , 100 * ratio_to_report(sal) over () percentage
  4    from mytable
  5  /

  EMP_ID      SAL PERCENTAGE
-------- -------- ----------
     101    10000   22,22222
     102    15000   33,33333
     103    20000   44,44444

3 rijen zijn geselecteerd.

SQL> select emp_id
  2       , sum(sal) sal
  3       , sum(percentage) percentage
  4    from ( select emp_id
  5                , sal
  6                , 100 * ratio_to_report(sal) over () percentage
  7             from mytable
  8         )
  9   group by rollup(emp_id)
 10  /

  EMP_ID      SAL PERCENTAGE
-------- -------- ----------
     101    10000   22,22222
     102    15000   33,33333
     103    20000   44,44444
            45000        100

4 rijen zijn geselecteerd.

Regards,
Rob.

My solution prevents inline view B-)

select ID,
sum(sal) as sal,
100*Ratio_To_Report(sum(sal))
over(partition by grouping(ID)) as percentage
from myTable
group by RollUp(ID)
order by ID;

Really nice to prvent the inline view like this !

But I doubt if it is effective to answer questions that are 4 days old ...

Regards,
Rob.

There is another method to prevent the InLineView ;-)

select emp_id,
sum(sal) as sal,
200*Ratio_To_Report(sum(sal))
over() as percentage
from myTable
group by RollUp(emp_id)
order by emp_id;