Geek Answers Handbook

Attach 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 three columns: HRS_SOURCE_ID, HRS_SOURCE_NAME, COUNT. But I want to combine two COUNTinto one query. The second request is identical to the previous one, but only STATUS_CODEshould be equal to "060", while in the first request its value is equal to "010". How to combine two COUNTtogether in one query, so that the total will be 4 columns? Help solve the problem.

+4
source share
1 answer

You can use case expression to get different values:

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

Note the OR in the where clause.

:

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

, else. 060, , , null. Null count.

+8

More articles:


All Articles