SELECT
PT.PF_CODE AS 都道府県コード
,R.PF_NAME AS 都道府県名
,test.SUM1 AS 総人口2015年
,test2.SUM2 AS 総人口2020年
,ROUND(CAST(test2.SUM2 as REAL) / CAST(test.SUM1 as REAL) * 100) AS 人口増加率
FROM POPU_TRANSITION AS PT
JOIN PREFECTURE AS R ON R.PF_CODE = PT.PF_CODE
JOIN (SELECT PF_CODE , SUM(TOTAL_AMT) AS SUM1 FROM POPU_TRANSITION WHERE SURVEY_YEAR = 2015 GROUP BY PF_CODE) AS test ON test.PF_CODE = PT.PF_CODE
JOIN (SELECT PF_CODE , SUM(TOTAL_AMT) AS SUM2 FROM POPU_TRANSITION WHERE SURVEY_YEAR = 2020 GROUP BY PF_CODE) AS test2 ON test2.PF_CODE = PT.PF_CODE
WHERE test.SUM1 > test2.SUM2
GROUP BY PT.PF_CODE
ORDER BY 人口増加率 DESC,PT.PF_CODE ASC