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;
HRS_SOURCE_ID
HRS_SOURCE_NAME
COUNT
COUNT
STATUS_CODE
COUNT
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
.