UNION ALL
SELECT
hospId,
hospName,
SUM( outNumber ) AS outNumber,
sum( inNumber ) AS inNumber,
SUM( unNumber ) AS unNumber
FROM
(
SELECT
CHANGE_HOSP_ID AS hospId,
CHANGE_HOSP_NAME AS hospName,
COUNT( 1 ) AS outNumber,
0 AS inNumber,
0 AS unNumber
FROM
CHRONIC_VISIT_PATIENT_FEEDBACK
WHERE
STATUS != 3
GROUP BY
CHANGE_HOSP_ID,
CHANGE_HOSP_NAME UNION ALL
SELECT
ACCEPT_HOST_ID AS hospId,
ACCEPT_HOSP_NAME AS hospName,
0 AS outNumber,
COUNT( 1 ) AS inNumber,
0 AS unNumber
FROM
CHRONIC_VISIT_PATIENT_FEEDBACK
WHERE
STATUS = 1
GROUP BY
ACCEPT_HOST_ID,
ACCEPT_HOSP_NAME UNION ALL
SELECT
ACCEPT_HOST_ID AS hospId,
ACCEPT_HOSP_NAME AS hospName,
0 AS outNumber,
0 AS inNumber,
COUNT( 1 ) AS unNumber
FROM
CHRONIC_VISIT_PATIENT_FEEDBACK
WHERE
STATUS = 2
GROUP BY
ACCEPT_HOST_ID,
ACCEPT_HOSP_NAME
)
GROUP BY
hospId,
hospName
FULL JOIN
WITH tab1 AS (
SELECT
CHANGE_HOSP_ID AS hospId,
CHANGE_HOSP_NAME AS hospName,
COUNT( 1 ) AS outNumber
FROM
CHRONIC_VISIT_PATIENT_FEEDBACK
WHERE
STATUS != 3
GROUP BY
CHANGE_HOSP_ID,
CHANGE_HOSP_NAME
),
tab2 AS (
SELECT
ACCEPT_HOST_ID AS hospId,
ACCEPT_HOSP_NAME AS hospName,
COUNT( 1 ) AS inNumber
FROM
CHRONIC_VISIT_PATIENT_FEEDBACK
WHERE
STATUS = 1
GROUP BY
ACCEPT_HOST_ID,
ACCEPT_HOSP_NAME
),
tab3 AS (
SELECT
ACCEPT_HOST_ID AS hospId,
ACCEPT_HOSP_NAME AS hospName,
COUNT( 1 ) AS unNumber
FROM
CHRONIC_VISIT_PATIENT_FEEDBACK
WHERE
STATUS = 2
GROUP BY
ACCEPT_HOST_ID,
ACCEPT_HOSP_NAME
) SELECT
nvl(
tab1.HOSPID,
nvl( tab2.HOSPID, tab3.HOSPID )) AS HOSPID,
nvl(
tab1.HOSPNAME,
nvl( tab2.HOSPNAME, tab3.HOSPNAME )) AS HOSPNAME,
tab1.outNumber,tab2.INNUMBER,tab3.unNumber
FROM tab1
FULL JOIN tab2 ON tab1.HOSPID = tab2.HOSPID
FULL JOIN tab3 ON tab2.HOSPID = tab3.HOSPID