SELECT
    ED.CODE
    ,ED.NAME
    ,ROUND(CAST(ED.CNT as REAL))
    ,TT.CNT AS TTCNT
    ,ROUND(CAST(ED.CNT as REAL) / CAST(TT.CNT as REAL),4)AS AP
    FROM
    DRINK_HABITS DH
    INNER JOIN
    (
        SELECT
            MAX(PF.PF_CODE) AS CODE 
            ,MAX(PF.PF_NAME) AS NAME 
            ,MAX(SS.CNT) CNT
            FROM
            PREFECTURE PF
            INNER JOIN (
                SELECT
                    PF_CODE
                    ,COUNT(*) AS CNT
                FROM
                    DRINK_HABITS
                WHERE
                    GENDER_CODE <> '1'
                    AND CATEGORY_CODE = '120' 
                GROUP BY
                PF_CODE
                ) SS
            ON SS.PF_CODE = PF.PF_CODE
        WHERE
            1 = 1
        GROUP BY
        PF.PF_CODE
    ) ED
    ON DH.PF_CODE = ED.CODE
    INNER JOIN
    (
        SELECT
            MAX(PF.PF_CODE) AS CODE 
            ,MAX(PF.PF_NAME) AS NAME 
            ,MAX(SS.CNT) CNT
            FROM
            PREFECTURE PF
            INNER JOIN (
                SELECT
                    PF_CODE
                    ,COUNT(*) AS CNT
                FROM
                    DRINK_HABITS
                WHERE
                    GENDER_CODE <> '1'
                GROUP BY
                PF_CODE
                ) SS
            ON SS.PF_CODE = PF.PF_CODE
        WHERE
            1 = 1
        GROUP BY
        PF.PF_CODE
    ) TT
    ON DH.PF_CODE = TT.CODE
    WHERE
    1 = 1
    GROUP BY
    TT.CODE;