Forum Stats

  • 3,757,564 Users
  • 2,251,245 Discussions
  • 7,869,867 Comments

Discussions

reg small query help

user586
user586 Member Posts: 378 Bronze Badge
edited Apr 14, 2009 8:35AM in SQL & PL/SQL
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
    SIS Member Posts: 615 Bronze Badge
    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
    ushitaki Member Posts: 1,128
    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
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 30, 2008 2:44AM
    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
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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;
This discussion has been closed.