SELECT s1.AGE
,s1.GENDER
,s1.H_AVG
,cast(ROUND(cast(SUM (CASE WHEN CATEGORY_CODE = 10 and s1.H_AVG > AVERAGE_VALUE THEN 1 ELSE 0 END) as REAL) / SUM (CASE WHEN CATEGORY_CODE = 10 THEN 1 ELSE 0 END) * 100,1) as TEXT) || '%' as H_PER
,s1.W_AVG
,cast(ROUND(cast(SUM (CASE WHEN CATEGORY_CODE = 20 and s1.W_AVG > AVERAGE_VALUE THEN 1 ELSE 0 END) as REAL) / SUM (CASE WHEN CATEGORY_CODE = 20 THEN 1 ELSE 0 END) * 100,1) as TEXT) || '%' as W_PER
-- ,AVERAGE_VALUE
-- ,CATEGORY_CODE
FROM (
SELECT
 AGE as  AGE
 --,GENDER_CODE('20'は 'MALE' 、'30'は 'FEMALE' と表示する) as  GENDER
 ,CASE WHEN GENDER_CODE = 20 THEN 'MALE' 
       WHEN GENDER_CODE = 30 THEN 'FEMALE' END AS GENDER
 ,ROUND(avg(CASE WHEN CATEGORY_CODE = 10 THEN AVERAGE_VALUE END),1) AS H_AVG
--  ,身長平均値以上の割合 as  H_PER
--  ,COUNT(CASE WHEN ROUND(avg(CASE WHEN CATEGORY_CODE = 10 THEN AVERAGE_VALUE END),2) > AVERAGE_VALUE THEN 1 END) AS H_PER
 ,ROUND(avg(CASE WHEN CATEGORY_CODE = 20 THEN AVERAGE_VALUE END),1) AS W_AVG
--  ,体重平均値以上の割合 as  W_PER
 ,GENDER_CODE
FROM 
 SCHOOL_HEALTH
WHERE
 SURVEY_YEAR = '2019'
GROUP BY 
 AGE,GENDER_CODE
) as s1 INNER JOIN SCHOOL_HEALTH s2 
on s1.AGE = s2.AGE 
and s2.SURVEY_YEAR = '2019'
and s1.GENDER_CODE = s2.GENDER_CODE
GROUP BY 
 s1.AGE,s1.GENDER
ORDER BY 
 s1.AGE desc
 ,s1.GENDER_CODE desc
;