How to operate with Union

Question

Guys I’m trying to do an operation with this two query but I don’t know how.

select 
sum(total_atend) total_atend,
sum(total_infect) total_infet

From (
select count(atendime.tp_atendimento) total_atend, 0 total_infect
from atendime, paciente
where atendime.cd_paciente = paciente.cd_paciente
and tp_atendimento = 'I'
and to_char(dt_atendimento,'mm/yyyy') = '12/2014'
---and cd_ori_ate = 3
and cd_atendimento_pai is null

union 

select 0 total_atend, count(tp_infec) total_infect from reg_inf total_infect
where to_char(dt_reg_inf,'mm/yyyy') = '12/2014' ) 

That one select above returns the data

What I need is to do an operation with these 2 selects

to return to me:

Total_atend, Total_Infet, Taxa_Infec* 

*(that one Taxa_Infec would be the total_infet/total_atend x 100)

Answer 1

Hmm, I couldn’t test it here, but this should work:

select 
sum(total_atend) total_atend,
sum(total_infect) total_infet,
((sum(total_infect)/sum(total_atend) ) * 100) taxa_infec

From (
select count(atendime.tp_atendimento) total_atend, 0 total_infect
from atendime, paciente
where atendime.cd_paciente = paciente.cd_paciente
and tp_atendimento = 'I'
and to_char(dt_atendimento,'mm/yyyy') = '12/2014'
---and cd_ori_ate = 3
and cd_atendimento_pai is null

union 

select 0 total_atend, count(tp_infec) total_infect from reg_inf total_infect
where to_char(dt_reg_inf,'mm/yyyy') = '12/2014' )