CODEWITHSUNDEEP
sqloracle-database
chihao
chihao

Reputation: 3

joining 2 subquerys

select count(cou_code) as Changes
from sdrp15_cosd    
where sd_code in
(select sd_code from sdrp15_submission_log
where QA_date is  null)

It gives me the result

Changes | 629

select count(cou_code) as Complete
from sdrp15_cosd    
where sd_code in
(select sd_code from sdrp15_submission_log
where QA_date is not null)

It gives me result

Completed | 210

I want to have 2 columns one named changes and one named complete with the two of those queries(above) combined into one query

Upvotes: 0

Views: 50

Answers (2)

Naved Munshi
Naved Munshi

Reputation: 507

Try UNION

select count(cou_code) as Changes from sdrp15_cosd where sd_code in
(select sd_code from sdrp15_submission_log where QA_date is  null) 
UNION
select count(cou_code) as Complete from sdrp15_cosd where sd_code in
(select sd_code from sdrp15_submission_log where QA_date is not null)

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Do two conditional count (with CASE), one for the is null's and one for the is not null's.

select count(case when sd_code in (select sd_code from sdrp15_submission_log
                                   where QA_date is null) then 1 end) as Changes,
       count(case when sd_code in (select sd_code from sdrp15_submission_log
                                   where QA_date is not null) then 1 end) as Complete
from sdrp15_cosd

Upvotes: 1

Related Questions