Join Two COUNT() in one SQL Query

Question

I have the following Oracle SQL Query:

SELECT ST.hrs_source_id, 
       TT.hrs_source_name, 
       Count(*) 
FROM   ps_hrs_app_profile P, 
       ps_hrs_rcmnt_sts S, 
       ps_hrs_al_log_srce ST, 
       ps_hrs_source TT 
WHERE  ST.hrs_source_id = TT.hrs_source_id 
       AND P.hrs_person_id = S.hrs_person_id 
       AND P.hrs_al_log_id = ST.hrs_al_log_id 
       AND S.status_code = '010' 
       AND S.status_dt BETWEEN '01-JAN-09' AND '31-MAR-16' 
GROUP  BY ST.hrs_source_id, 
          TT.hrs_source_name 
ORDER  BY TT.hrs_source_name ASC; 

The above query produces results with 3 columns: HRS_SOURCE_ID, HRS_SOURCE_NAME, COUNT. But I want to join two COUNTs into one query. The second query is identical as above, but only STATUS_CODE should be equal to '060', while in first query its equal to '010'. How to join two COUNTs together in one Query, so total will be 4 columns? Please help to solve it.

Answer 1

You can use a case expression to get the different counts:

SELECT ST.HRS_SOURCE_ID, 
       TT.HRS_SOURCE_NAME, 
       COUNT(case when S.STATUS_CODE = '060' then 1 end) as cnt60, 
       count(case when S.STATUS_CODE = '010' then 1 end) as cnt10
FROM PS_HRS_APP_PROFILE P, PS_HRS_RCMNT_STS S, PS_HRS_AL_LOG_SRCE ST, PS_HRS_SOURCE TT
WHERE ST.HRS_SOURCE_ID = TT.HRS_SOURCE_ID
   AND P.HRS_PERSON_ID = S.HRS_PERSON_ID
   AND P.HRS_AL_LOG_ID = ST.HRS_AL_LOG_ID
   AND (S.STATUS_CODE = '010' or S.STATUS_CODE = '060')
   AND S.STATUS_DT BETWEEN '01-JAN-09' AND '31-MAR-16'
GROUP BY ST.HRS_SOURCE_ID, TT.HRS_SOURCE_NAME
ORDER BY TT.HRS_SOURCE_NAME ASC

Notice the OR in the where clause.

You may better understand a sum combined with count:

sum(case when S.STATUS_CODE = '060' then 1 else 0 end) cnt60

To explain the case in the query, there is no else. If status is different from 060 then the value returned by case is null. Null is not counted by count.