Using formula in SQL

Question

Hello, I’m having a doubt and I can not find examples to progress, following:

select  
    'disponivel' as TIPO  
    , COUNT(distinct dp.ds_nome) as qtd  
FROM  
    dm_pessoa_treino dp  
union ALL  
select  
    'em_uso' as TIPO  
    ,COUNT(distinct nm_cpf) as qtd  
FROM  
    ft_pessoa_treino fp  

Resultado:  
|tipo       |qtd |  
|disponivel |2845|  
|em_uso     |2848|

Proposição:
Quero poder exibir um resultado com a seguinte equação: N(disp.) * 100 / N(uso)  
usando como base os resultados de COUNT.

Answer 1

I removed the fields you set as Type, separated the two selects and made a select that already makes the account with the other select. In Firebird would look like this:

select
    (
    (select
       COUNT(distinct dp.ds_nome) as qtd
    FROM  
        dm_pessoa_treino dp  ) *100)
     /
    (select
        COUNT(distinct nm_cpf) as qtd
    FROM  
        ft_pessoa_treino fp)
    )
from
    rdb$database

Answer 2

Flavio, I don’t know which database you’re using, but follow a suggestion for testing:

with CTE_Qtds as
(
  select  
    'disponivel' as TIPO  
    ,COUNT(distinct dp.ds_nome) as qtd  
  FROM dm_pessoa_treino dp  

  union ALL  

  select  
    'em_uso' as TIPO  
    ,COUNT(distinct nm_cpf) as qtd  
  FROM ft_pessoa_treino fp  
)

select * 
from CTE_Qtds

union all

select 
  'resultado', 
  100.0 * d.qtd / u.qtd
from CTE_Qtds as d
cross join CTE_Qtds as u
where 
  d.tipo = 'disponível' and 
  u.tipo = 'em_uso'

I hope it helps