with pref_sum as(
    select
        h.pf_code,
        max(p.pf_name) as pf_name,
        sum(INP_YES) as sum_yes,
        sum(INP_NO) as sum_no,
        sum(UNIDENTIFIED) as sum_unidentified,
        sum(INP_YES) + sum(INP_NO) + sum(UNIDENTIFIED) as total,
        
        (cast(sum(INP_YES)  as float)/cast((sum(INP_YES) + sum(INP_NO) + sum(UNIDENTIFIED) ) as float)) * 100 as rate
    from
        HOSPITALIZATION h
        left join
            PREFECTURE p
        on  h.PF_CODE = p.PF_CODE
    group by
        p.pf_code
)
select
    pref.pf_code as 都道府県コード,
    pref.pf_name as 都道府県名,
    -- sum_yes,
    -- sum_no,
    -- sum_unidentified, 
    -- total,
    -- rate,
    round(rate, 1) as 入院率
from
    pref_sum pref
group by
    pref.pf_code,
    pref.pf_name
order by (cast(sum_yes as float)/cast(total as float))  desc
;