programmers : 진료과별 총 예약 횟수 출력하기

진료과별 총 예약 횟수 출력하기

 

MYSQL

SELECT MCDP_CD AS '진료과코드', COUNT(APNT_NO) AS '5월예약건수'
FROM APPOINTMENT 
WHERE MONTH(APNT_YMD) = 5
GROUP BY MCDP_CD
ORDER BY COUNT(APNT_NO), MCDP_CD

 

ORACLE

SELECT MCDP_CD "진료과코드", COUNT(APNT_NO) "5월예약건수"
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD,'MM') = '05' -- TO_CHAR(APNT_YMD,'YYYY-MM') = '2022-05'
GROUP BY MCDP_CD
ORDER BY 2, 1

# CASE  

SELECT "진료과코드", "5월예약건수"
FROM(SELECT MCDP_CD AS "진료과코드",
SUM(CASE WHEN TO_CHAR(APNT_YMD, 'MM') = '05' THEN 1 END) AS "5월예약건수"
FROM APPOINTMENT    
GROUP BY MCDP_CD)
WHERE "5월예약건수" IS NOT NULL
ORDER BY "5월예약건수" ASC, "진료과코드" ASC

# BETWEEN

SELECT MCDP_CD AS "진료과코드" , COUNT(*) AS "5월예약건수"
FROM APPOINTMENT
WHERE APNT_YMD 
BETWEEN TO_DATE('2022-05-01', 'YYYY-MM-DD') AND
TO_DATE('2022-05-31', 'YYYY-MM-DD')
GROUP BY MCDP_CD
ORDER BY "5월예약건수", "진료과코드"