Result from query 1 - result from query 2

[thisisboni]

Hi:

I have the following 2 queries:

SELECT COUNT(DISTINCT a.cnslt_id)
FROM iplanrpt.vm_rpt_cnslt_ctrct a
where a.bmsdmn_nm = 'ABILIFY'
and a.ctrc_status_ds = 'Active'


SELECT count(distinct a.srch_cnf_spek_hcp_id) as "used"
FROM iplanrpt.vm_rpt_spek_epsd a, iplanrpt.vm_rpt_prgm b
where a.bmsdmn_nm = 'Abilify'
and a.srch_cnf_spek_hcp_id is not null
and a.prgm_id = b.prgm_id
and b.prgm_start_dtm >= to_date('01/01/2010','mm/dd/yyyy')
and b.prgm_start_dtm < to_date('04/01/2010','mm/dd/yyyy')
and b.prgm_state_id in (6,7)
How do I get COUNT(DISTINCT a.cnslt_id) - count(distinct a.srch_cnf_spek_hcp_id) in one query ?

Thanks

 

[thisisboni]

Got the solution !!

select ac - used
from
(SELECT COUNT(DISTINCT a.cnslt_id) ac
FROM iplanrpt.vm_rpt_cnslt_ctrct a
where a.bmsdmn_nm = 'ABILIFY'
and a.ctrc_status_ds = 'Active'
),
(SELECT count(distinct a.srch_cnf_spek_hcp_id) used
FROM iplanrpt.vm_rpt_spek_epsd a, iplanrpt.vm_rpt_prgm b
where a.bmsdmn_nm = 'Abilify'
and a.srch_cnf_spek_hcp_id is not null
and a.prgm_id = b.prgm_id
and b.prgm_start_dtm >= to_date('01/01/2010','mm/dd/yyyy')
and b.prgm_start_dtm < to_date('04/01/2010','mm/dd/yyyy')
and b.prgm_state_id in (6,7))