select
PF_CODE as 都道府県コード,
PF_NAME as 都道府県名,
SUM(y_2015) as 総人口2015年,
SUM(y_2020) as 総人口2020年,
round(100.0*SUM(y_2020)/SUM(y_2015),0) as 人口増加率
from (select
pop.PF_CODE,
pre.PF_NAME,
(case pop.SURVEY_YEAR when 2015 then pop.TOTAL_AMT when 2020 then 0 else NULL END) as y_2015,
(case pop.SURVEY_YEAR when 2020 then pop.TOTAL_AMT when 2015 then 0 else NULL END) as y_2020
from POPU_TRANSITION as pop
inner join PREFECTURE as pre
on pop.PF_CODE=pre.PF_CODE)as t
group by PF_NAME
order by 人口増加率 DESC,都道府県コード ASC