4 Replies Latest reply: Nov 9, 2012 8:17 AM by Peter vd Zwan

# Ratio

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
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
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
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
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