-- select pr.PFCODE,pr.PFNAME from PREFECTURE pr
--     inner join POPU_TRANSISON po
--     on sum(case when SURVEY_YEAR == 2015 THEN TOTAL_AMT ELSE 0 END)
select distinct po.PF_CODE as 都道府県コード, 
    pre.PF_NAME as 都道府県名,
    sum(case when po.SURVEY_YEAR == 2015 THEN TOTAL_AMT ELSE 0 END) as 総人口2015年,
    sum(case when po.SURVEY_YEAR == 2020 THEN TOTAL_AMT ELSE 0 END) as 総人口2020年,
    round(
        cast(sum(case when po.SURVEY_YEAR == 2020 THEN TOTAL_AMT ELSE 0 END) as real)/
        cast(sum(case when po.SURVEY_YEAR == 2015 THEN TOTAL_AMT ELSE 0 END) as real) * 100
    )as 人口増加率
from POPU_TRANSITION po
    inner join PREFECTURE pre
        on po.PF_CODE == pre.PF_CODE
group by po.PF_CODE
order by 人口増加率 desc,都道府県コード asc;