SELECT
	HOSPITALIZATION.PF_CODE AS [都道府県コード]
	,PREFECTURE.PF_NAME AS [都道府県名]
	,ROUND(
		CASE
			WHEN SUM(
					IFNULL(INP_YES, 0)
		 			+ IFNULL(INP_NO, 0)
		 			+ IFNULL(UNIDENTIFIED, 0)
				) = 0
			THEN 0
			ELSE CAST(
					SUM(
						IFNULL(INP_YES, 0)
					)
				 AS REAL)
				 / 
				CAST(
					SUM(
						IFNULL(INP_YES, 0)
			 			+ IFNULL(INP_NO, 0)
			 			+ IFNULL(UNIDENTIFIED, 0)
					)
				 AS REAL)
				 * 100
		END
	, 1) AS [入院率]
FROM HOSPITALIZATION
INNER JOIN PREFECTURE
ON PREFECTURE.PF_CODE = HOSPITALIZATION.PF_CODE
GROUP BY
	HOSPITALIZATION.PF_CODE
	,PREFECTURE.PF_NAME
ORDER BY
    CAST(ROUND(
		CASE
			WHEN SUM(
					IFNULL(INP_YES, 0)
		 			+ IFNULL(INP_NO, 0)
		 			+ IFNULL(UNIDENTIFIED, 0)
				) = 0
			THEN 0
			ELSE CAST(
					SUM(
						IFNULL(INP_YES, 0)
					)
				 AS REAL)
				 / 
				CAST(
					SUM(
						IFNULL(INP_YES, 0)
			 			+ IFNULL(INP_NO, 0)
			 			+ IFNULL(UNIDENTIFIED, 0)
					)
				 AS REAL)
				 * 100
		END
	, 1) AS REAL) DESC
    ,HOSPITALIZATION.PF_CODE ASC
;