SELECT 
    pt.PF_CODE as '都道府県コード'
    , PF_NAME as '都道府県名'
    , SUM(
        CASE WHEN SURVEY_YEAR = 2015
            THEN TOTAL_AMT
            ELSE 0
            END
        ) as '総人口2015年'
    , SUM(
        CASE WHEN SURVEY_YEAR = 2020
            THEN TOTAL_AMT
            ELSE 0
            END
        ) as '総人口2020年'
    , ROUND(100 * SUM(
        CASE WHEN SURVEY_YEAR = 2020
            THEN TOTAL_AMT
            ELSE 0
            END
        ) / 
        SUM(
        CASE WHEN SURVEY_YEAR = 2015
            THEN TOTAL_AMT
            ELSE 0
            END
        ), 0) as '人口増加率'
        
FROM 
    (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , CAST(TOTAL_AMT AS REAL) as TOTAL_AMT
    FROM
        POPU_TRANSITION
    )as pt
LEFT JOIN
        PREFECTURE as p
    ON
        pt.PF_CODE = p.PF_CODE
WHERE
    SURVEY_YEAR IN (2015, 2020)
GROUP BY 
    pt.PF_CODE
HAVING
    人口増加率 > 100
ORDER BY
    人口増加率 DESC
    , 都道府県コード ASC
;