Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

reg small query help

user586Mar 9 2007 — edited Apr 14 2009
i have a table like this

this is the table .

Emp_id SAL %SAL
101 10000 (10000*100/45000)
102 15000
103 20000
---------
Sum(sal) 45000
---------


We need a query to calculate the % like this i.e sal*100/sum(sal) without using group by clause.

please help me

Comments

SIS

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
ushitaki 
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)
;
Rob van Wijk 
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.

Aketi Jyuuzou
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;
Rob van Wijk
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.
Aketi Jyuuzou
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;
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 12 2009
Added on Mar 9 2007
6 comments
3,582 views