WITH order_t AS(
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , KIND_CODE
        , TOTAL_VALUE
        , ROW_NUMBER() OVER (ORDER BY TOTAL_VALUE DESC) AS row_id
        , COUNT(*) OVER () AS ct
    FROM
        CONVENIENCE
    WHERE
        SURVEY_YEAR = 2019
        AND KIND_CODE = '100'
    GROUP BY 1,2,3,4
)
SELECT DISTINCT
    CASE
        WHEN (MOD(ct, 2) = 0)
            THEN (
                SELECT
                    ROUND(AVG(TOTAL_VALUE)) AS SA_MEDIAN
                FROM
                    order_t
                WHERE
                    row_id BETWEEN ct/2.0 and ct/2.0 + 1
            )
        ELSE( 
            SELECT
                ROUND(TOTAL_VALUE) AS SA_MEDIAN
            FROM
                order_t
            WHERE
                row_id = ROUND(ct/2.0)
            )
    END AS SA_MEDIAN
FROM
    order_t;