Mikhail Mikhail - 7 months ago 9
SQL Question

Join Two COUNT() in one SQL Query

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
COUNT
s 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
COUNT
s together in one Query, so total will be 4 columns? Please help to solve it.

Answer

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.