select
    pt.pf_code 都道府県コード, 
    p.pf_name 都道府県名,
    sum(case when pt.survey_year = 2015 then pt.total_amt else 0 end) 総人口2015年,
    sum(case when pt.survey_year = 2020 then pt.total_amt else 0 end) 総人口2020年,
    round(
        cast(sum(case when pt.survey_year = 2020 then pt.total_amt else 0 end) as real) * 100
        /
        cast(sum(case when pt.survey_year = 2015 then pt.total_amt else 0 end) as real)
    , 0) 人口増加率
from POPU_TRANSITION pt
join PREFECTURE p on pt.pf_code = p.pf_code
where 1 = 1
and pt.survey_year in (2015, 2020)
group by pt.pf_code, p.pf_name
having
    sum(case when pt.survey_year = 2020 then pt.total_amt else 0 end) 
    >
    sum(case when pt.survey_year = 2015 then pt.total_amt else 0 end) 
order by 人口増加率 desc, 都道府県コード asc
;