with pre1 as (
select
    pf_code
    , cast(inp_yes as real) as inp_yes
    , cast(inp_no as real) as inp_no
    , cast(unidentified as real) as unidentified
from 
    hospitalization
),
pre2 as (
    select
        pf_code
        , sum(inp_yes) as sum_yes
        , sum(inp_no) as sum_no
        , sum(unidentified) as sum_uni
    from    
        pre1
    group by 
        pf_code
),
pre3 as (
    select
        pf_code
        , sum_yes/(sum_yes + sum_no + sum_uni)*100 as wariai
    from 
        pre2
)


select
    b.PF_NAME as '都道府県名'
    , b.pf_code as '都道府県コード'
    , case when (wariai % 1) > 0 then round(wariai, 0) else round(wariai, 2) end as '入院率'
from
    pre3 as a
left join 
    PREFECTURE as b
on 
    a.pf_code = b.PF_CODE