with pref_sum AS (
    select
        PF_CODE AS PF_CODE
        , SUM(INP_YES) AS YES_SUM
        , SUM(INP_NO) AS NO_SUM
        , SUM(UNIDENTIFIED) AS UNDEFITIED_SUM
    from
        HOSPITALIZATION
    group by
        PF_CODE
)
,pref_sum2 AS (
    select
        PF_CODE AS PF_CODE
        , cast(YES_SUM as float)  AS TOP
        , cast(YES_SUM + NO_SUM + UNDEFITIED_SUM as float) AS BOTTOM
    from
        pref_sum
),
pref_sum3 AS (
    select
        PF_CODE,
        ROUND(cast(TOP as float) * 100 / BOTTOM, 1) AS ratio
    from
        pref_sum2
),
join_table AS (
    select
        pref_sum3.PF_CODE,
        pref_sum3.ratio,
        PREFECTURE.PF_NAME
    from pref_sum3 
        left outer join PREFECTURE
        on pref_sum3.PF_CODE = PREFECTURE.PF_CODE
)


select
    PF_CODE AS 都道府県コード
    , PF_NAME AS 都道府県名
    , ratio AS 入院率
from 
    join_table
order by
    ratio desc,
    PF_CODE
;