select
PREFECTURE.PF_CODE as 都道府県コード,
PREFECTURE.PF_NAME as 都道府県名,
h2.RATE as 入院率
from
PREFECTURE
inner join
(
    select
        h.PF_CODE,
        round(cast (h.INP_YES * 100 as real) / cast(h.INP_SUM as real), 1) as RATE
    from
    (
    select
        PF_CODE,
        sum(INP_YES) as INP_YES,
        sum(INP_YES+INP_NO+UNIDENTIFIED) as INP_SUM
    from
        HOSPITALIZATION
    group by
        PF_CODE
    ) as h
) as h2
on h2.PF_CODE = PREFECTURE.PF_CODE
order by
h2.RATE desc,
PREFECTURE.PF_CODE asc;