WITH base AS (
	SELECT
			PF_CODE
			,TOTAL_VALUE
			,totalrank
	FROM (
		SELECT
			PF_CODE
			,TOTAL_VALUE
			,RANK() OVER(ORDER BY TOTAL_VALUE DESC) AS totalrank
		FROM CONVENIENCE
		WHERE SURVEY_YEAR = 2019
			AND KIND_CODE = '100'
		)
	)
, datacnt AS (
	SELECT COUNT(*) AS countdata
	FROM base
)
,keisan AS (
	SELECT t0.TOTAL_VALUE,t0.totalrank
	FROM base AS t0
	WHERE 
		CASE WHEN --偶数奇数の場合分け
		(SELECT countdata FROM datacnt)/2 = ROUND((SELECT countdata FROM datacnt)/2,0) THEN
			t0.totalrank IN (ROUND((SELECT countdata FROM datacnt)/2,0),ROUND((SELECT countdata FROM datacnt)/2,0)+1)
		ELSE
			t0.totalrank IN (ROUND((SELECT countdata FROM datacnt)/2,0)) END
)
SELECT 
	ROUND(AVG(TOTAL_VALUE)) AS SA_MEDIAN
FROM keisan