1 Reply Latest reply: Aug 26, 2013 5:19 AM by Frank Kulash RSS

    Query Help !

    user11156570

      Hello Experts

       

      I am using Oracle Database 10g

       

      My output is

      {code}

      Empno---Contact_Empno---Weight_Factor

      1-------------020-------------------0.5

      1------------024--------------------0.5

      2---------------022------------------1

      3----------------054-----------------0.33

      3---------------072-------------------0.33

      3------------------055---------------0.33

      {code}

       

      I want to determine weight factor

      i.e weight factor based on empno . If empno is repeated twice then formula for weight factor should be 1/2 = 0.5 , if empno is repeated thrice then WF should be 1/3 = 0.33 .

       

      how can it achieved? from sql?

       

      please help

        • 1. Re: Query Help !
          Frank Kulash

          Hi,

           

          One way is to use the analytic COUNT function:

           

          1 / COUNT (*) OVER (PARTITION BY empno)

           

          I don't have your table, so I'll use scott.emp to illustrate (with weight_factor based on job, not on empno):

           

          SELECT    job

          ,         ename

          ,         1 / COUNT (*) OVER (PARTITION BY job)   AS weight_factor

          FROM      scott.emp

          ORDER BY  job

          ;


          Output:

           

          JOB       ENAME      WEIGHT_FACTOR

          --------- ---------- -------------

          ANALYST   SCOTT                .50

          ANALYST   FORD                 .50

          CLERK     MILLER               .25

          CLERK     JAMES                .25

          CLERK     SMITH                .25

          CLERK     ADAMS                .25

          MANAGER   BLAKE                .33

          MANAGER   JONES                .33

          MANAGER   CLARK                .33

          PRESIDENT KING                1.00

          SALESMAN  TURNER               .25

          SALESMAN  MARTIN               .25

          SALESMAN  WARD                 .25

          SALESMAN  ALLEN                .25