with sub as (
    select
        total_value
        , rank() over (order by total_value desc) as rankval
        , kind_code
        , survey_year
    from
        convenience
    where
        SURVEY_YEAR = 2019
        and KIND_CODE = '100'
)
select
    round(sum(sub2.total_value*1.0) / count(sub2.total_value)) as SA_MEDIAN
from (
    select
        total_value
        , rankval
    from sub
    where
        case when (select max(rankval) from sub) = 1
            then rankval = 1
        when (select max(rankval)%2 from sub) = 0
            then rankval in (
                (select max(rankval)/2 from sub)
                , (select max(rankval)/2 from sub) + 1
            )
        when (select max(rankval)%2 from sub) = 1
            then rankval in (
                (select max(rankval)/2 from sub) + 1
            )
        end
) as sub2
;