select pf.PF_CODE AS 都道府県コード, pf.PF_NAME AS 都道府県名, coalesce(s1.a,0) AS 総人口2015年,coalesce(s2.a,0) as 総人口2020年,cast(round(cast(s2.a as real)/cast(s1.a as real)*100) as integer) as 人口増加率 from PREFECTURE pf left outer join ( select PF_CODE,SUM(TOTAL_AMT) AS a from POPU_TRANSITION WHERE SURVEY_YEAR = 2015 GROUP BY PF_CODE)s1 on (s1.PF_CODE = pf.PF_CODE) left outer join (select PF_CODE,SUM(TOTAL_AMT) as a from POPU_TRANSITION WHERE SURVEY_YEAR = 2020 GROUP BY PF_CODE)s2 on (s2.PF_CODE = pf.PF_CODE) where s2.a>s1.a
order by cast(round(cast(s2.a as real)/cast(s1.a as real)*100) as integer) desc, pf.PF_CODE;