This discussion is archived
4 Replies Latest reply: Nov 9, 2012 6:17 AM by Peter vd Zwan RSS

Ratio

948191 Newbie
Currently Being Moderated
Hi i need to calculate ratio of each manager with total number of employees vs total number of devices.
e.g total number of employees per manager 179 and devices 579
employees 38 devices 54
i want to display ratio in my report on Ratio column as manager1 1:2,manager2 1: 1 how can i do that, itried to divide the total number of employees over devices and gives .2233 and i cant get my mind into dispalying it as 1:2 or 1:1 please assist
  • 1. Re: Ratio
    NextName Newbie
    Currently Being Moderated
    The math is simple, the SQL untested:
    SELECT 
        manager_id, 
        employees,
        devices, 
        '1:' || devices/employees
    FROM
        yourtable
    You want to enhance this with a CASE for special circumstances (either employees or devices might be Null or zero ...)!

    Edited by: NextName on Nov 9, 2012 1:12 PM
  • 2. Re: Ratio
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    NextName wrote:
    ... You want to enhance this with a CASE for special circumstances (either employees or devices might be Null or zero ...)!
    For example:
    CASE
        WHEN  devices   >= employees
        AND       employees != 0  
               THEN  ROUND (devices/employees) || ':1'
        WHEN  devices   < employees
        AND       devices != 0  
               THEN '1:' ||  ROUND (employees/devices)
    END            AS device_employee_ratio
  • 3. Re: Ratio
    Kim Berg Hansen Expert
    Currently Being Moderated
    Just for fun in case one does not always wish for *1:* +<something>+ but wish for more precision:
    SQL> with r as (
      2     select .2233 ratio from dual union all
      3     select .2500 ratio from dual union all
      4     select .2666 ratio from dual union all
      5     select .2750 ratio from dual union all
      6     select .2828 ratio from dual
      7  )
      8  select r.ratio ratio_num
      9       , (
     10           select to_char(
     11                     max(level)
     12                   , 'TM9'
     13                  ) || ':' || to_char(
     14                     max(round(level / r.ratio)) keep (dense_rank last order by level)
     15                   , 'TM9'
     16                  )
     17           from dual
     18           connect by abs(((level-1) / round((level-1) / r.ratio) / r.ratio)-1) > 1
     19                  and level <= 1000
     20         ) ratio_txt
     21    from r
     22  /
    
     RATIO_NUM RATIO_TXT
    ---------- ------------
         ,2233 1:4
           ,25 1:4
         ,2666 1:4
          ,275 1:4
         ,2828 1:4
    The last figure on line 18 is the desired precision - how precise do you wish your ratio fraction to be.
    The 1000 on line 19 is to prevent a possible infinite loop.

    Observe as I increase the desired precision just by changing the last figure in line 18:
    SQL> with r as (
      2     select .2233 ratio from dual union all
      3     select .2500 ratio from dual union all
      4     select .2666 ratio from dual union all
      5     select .2750 ratio from dual union all
      6     select .2828 ratio from dual
      7  )
      8  select r.ratio ratio_num
      9       , (
     10           select to_char(
     11                     max(level)
     12                   , 'TM9'
     13                  ) || ':' || to_char(
     14                     max(round(level / r.ratio)) keep (dense_rank last order by level)
     15                   , 'TM9'
     16                  )
     17           from dual
     18           connect by abs(((level-1) / round((level-1) / r.ratio) / r.ratio)-1) > .1
     19                  and level <= 1000
     20         ) ratio_txt
     21    from r
     22  /
    
     RATIO_NUM RATIO_TXT
    ---------- ------------
         ,2233 2:9
           ,25 1:4
         ,2666 1:4
          ,275 1:4
         ,2828 2:7
    
    SQL> with r as (
      2     select .2233 ratio from dual union all
      3     select .2500 ratio from dual union all
      4     select .2666 ratio from dual union all
      5     select .2750 ratio from dual union all
      6     select .2828 ratio from dual
      7  )
      8  select r.ratio ratio_num
      9       , (
     10           select to_char(
     11                     max(level)
     12                   , 'TM9'
     13                  ) || ':' || to_char(
     14                     max(round(level / r.ratio)) keep (dense_rank last order by level)
     15                   , 'TM9'
     16                  )
     17           from dual
     18           connect by abs(((level-1) / round((level-1) / r.ratio) / r.ratio)-1) > .01
     19                  and level <= 1000
     20         ) ratio_txt
     21    from r
     22  /
    
     RATIO_NUM RATIO_TXT
    ---------- ------------
         ,2233 2:9
           ,25 1:4
         ,2666 4:15
          ,275 3:11
         ,2828 7:25
    
    SQL> with r as (
      2     select .2233 ratio from dual union all
      3     select .2500 ratio from dual union all
      4     select .2666 ratio from dual union all
      5     select .2750 ratio from dual union all
      6     select .2828 ratio from dual
      7  )
      8  select r.ratio ratio_num
      9       , (
     10           select to_char(
     11                     max(level)
     12                   , 'TM9'
     13                  ) || ':' || to_char(
     14                     max(round(level / r.ratio)) keep (dense_rank last order by level)
     15                   , 'TM9'
     16                  )
     17           from dual
     18           connect by abs(((level-1) / round((level-1) / r.ratio) / r.ratio)-1) > .001
     19                  and level <= 1000
     20         ) ratio_txt
     21    from r
     22  /
    
     RATIO_NUM RATIO_TXT
    ---------- ------------
         ,2233 21:94
           ,25 1:4
         ,2666 4:15
          ,275 11:40
         ,2828 13:46
    
    SQL> with r as (
      2     select .2233 ratio from dual union all
      3     select .2500 ratio from dual union all
      4     select .2666 ratio from dual union all
      5     select .2750 ratio from dual union all
      6     select .2828 ratio from dual
      7  )
      8  select r.ratio ratio_num
      9       , (
     10           select to_char(
     11                     max(level)
     12                   , 'TM9'
     13                  ) || ':' || to_char(
     14                     max(round(level / r.ratio)) keep (dense_rank last order by level)
     15                   , 'TM9'
     16                  )
     17           from dual
     18           connect by abs(((level-1) / round((level-1) / r.ratio) / r.ratio)-1) > .0001
     19                  and level <= 1000
     20         ) ratio_txt
     21    from r
     22  /
    
     RATIO_NUM RATIO_TXT
    ---------- ------------
         ,2233 23:103
           ,25 1:4
         ,2666 193:724
          ,275 11:40
         ,2828 69:244
    
    SQL> with r as (
      2     select .2233 ratio from dual union all
      3     select .2500 ratio from dual union all
      4     select .2666 ratio from dual union all
      5     select .2750 ratio from dual union all
      6     select .2828 ratio from dual
      7  )
      8  select r.ratio ratio_num
      9       , (
     10           select to_char(
     11                     max(level)
     12                   , 'TM9'
     13                  ) || ':' || to_char(
     14                     max(round(level / r.ratio)) keep (dense_rank last order by level)
     15                   , 'TM9'
     16                  )
     17           from dual
     18           connect by abs(((level-1) / round((level-1) / r.ratio) / r.ratio)-1) > .00001
     19                  and level <= 1000
     20         ) ratio_txt
     21    from r
     22  /
    
     RATIO_NUM RATIO_TXT
    ---------- ------------
         ,2233 23:103
           ,25 1:4
         ,2666 257:964
          ,275 11:40
         ,2828 97:343
    
    SQL> with r as (
      2     select .2233 ratio from dual union all
      3     select .2500 ratio from dual union all
      4     select .2666 ratio from dual union all
      5     select .2750 ratio from dual union all
      6     select .2828 ratio from dual
      7  )
      8  select r.ratio ratio_num
      9       , (
     10           select to_char(
     11                     max(level)
     12                   , 'TM9'
     13                  ) || ':' || to_char(
     14                     max(round(level / r.ratio)) keep (dense_rank last order by level)
     15                   , 'TM9'
     16                  )
     17           from dual
     18           connect by abs(((level-1) / round((level-1) / r.ratio) / r.ratio)-1) > .000001
     19                  and level <= 1000
     20         ) ratio_txt
     21    from r
     22  /
    
     RATIO_NUM RATIO_TXT
    ---------- ------------
         ,2233 1000:4478
           ,25 1:4
         ,2666 534:2003
          ,275 11:40
         ,2828 610:2157
    At this point my "level <= 1000" kicks in and says "no more - you are looping too much."
    But I have a 5 digit precision on my ratio fraction - that ought to be enough.

    Anyway, it might not be much useful, but it was fun coding ;-)
  • 4. Re: Ratio
    Peter vd Zwan Expert
    Currently Being Moderated
    Kim,

    Nice query.

    But as I understand the OP question, he has two integer numbers number of employees and number of devices.
    If there are for example 12 emp and 8 dev the ratio is 12:8
    This can be simplified to 3:2 without los of precision.

    To do this we need the Greatest Common Devisor of the two numbers (in the example above gcd(12,8) = 4).
    The below first calculates the GCD and then uses this to simplify the ratio without los of precision.
    with manager_list as
      (
      select 1 manager_id, 179  num_emp, 579  num_dev from dual union all
      select 2 manager_id, 38   num_emp, 54   num_dev from dual union all
      select 3 manager_id, 1000 num_emp, 10   num_dev from dual union all
      select 4 manager_id, 100  num_emp, 25   num_dev from dual union all
      select 5 manager_id, 356  num_emp, 34   num_dev from dual union all
      select 6 manager_id, 12   num_emp, 8    num_dev from dual
      )
    ,calc_gcd as
      (
      select
        manager_id
        ,gcd
      from
        manager_list
      model
      partition by (manager_id)
      dimension by (0 x)
      measures(greatest( num_emp, num_dev) gcd, least( num_emp, num_dev) b, 0 q, 0 r)
      rules iterate (1e5) until (r[0] = 0)
        (
        r[0] = mod(gcd[0],b[0])
        ,q[0] = (gcd[0] - r[0])/b[0]
        ,gcd[0] = b[0]
        ,b[0] = r[0]
        )
      )
    
    select
      a.manager_id
      ,a.num_emp
      ,a.num_dev
      ,b.gcd
      ,a.num_emp / b.gcd || ':' || a.num_dev / b.gcd   ratio
    
    from
      manager_list      a   join
      calc_gcd         b   on (a.manager_id = b.manager_id)
    
    order by
      manager_id
    ;
    
    
    MANAGER_ID NUM_EMP NUM_DEV GCD RATIO                                                                           
    ---------- ------- ------- --- ---------------------------------------------------------------------------------
             1     179     579   1 179:579                                                                           
             2      38      54   2 19:27                                                                             
             3    1000      10  10 100:1                                                                             
             4     100      25  25 4:1                                                                               
             5     356      34   2 178:17                                                                            
             6      12       8   4 3:2                                                                               
    Maybe usefull, maybe not.
    but fun to program anyway.

    Regards,

    Peter

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points