pcr_postive_pcr2_positive_and_pcr1neg__on_arv

select count(*) as pcr_postive_pcr2_positive_and_pcr1neg__on_arv from (

select b.* from (

SELECT id_patient,pcr_result FROM testing_specimen where pcr_result=1 and which_pcr=2 and  id_patient  in (

SELECT ab1.id_patient from (

SELECT id_patient,pcr_result FROM testing_specimen where pcr_result<>1 and which_pcr=1

union select id_patient,result as pcr_result from testing_result where result<>1 and which_pcr=1

) ab1

)

union select id_patient,result as pcr_result from testing_result where result=1 and which_pcr=2 and  id_patient  in (

SELECT ab2.id_patient from (

SELECT id_patient,pcr_result FROM testing_specimen where pcr_result<>1 and which_pcr=1

union select id_patient,result as pcr_result from testing_result where result<>1 and which_pcr=1) ab2)




) b

left join tracking_regime r on r.id_patient=b.id_patient

where r.id is not null group by b.id_patient




) a