--select T4.AGE, T4.GENDER, T4.H_AVG, ((double)T4.PF/T4.CNT * 100), T5.W_AVG, ((double)T5.PF/T5.CNT * 100)
--from
--(
select SH.AGE,
    CASE  
        WHEN SH.GENDER_CODE = '20' THEN 'MALE' 
        ELSE 'FEMALE' 
    END AS GENDER, T3.H_AVG, COUNT(SH.PF_CODE),T3.CNT
from SCHOOL_HEALTH AS SH inner join (
-- 身長の平均
select T1.AGE, T1.GENDER, T1.H_AVG, T2.W_AVG, T1.CNT  from (
select 
    AGE, 
    CASE  
        WHEN GENDER_CODE = '20' THEN 'MALE' 
        ELSE 'FEMALE' 
    END AS GENDER, 
    ROUND(AVG(AVERAGE_VALUE),1) AS H_AVG,
    COUNT(PF_CODE) AS CNT from SCHOOL_HEALTH
where SURVEY_YEAR  = 2019 and CATEGORY_CODE = '10'
group by GENDER_CODE, AGE, CATEGORY_CODE) T1
inner join
(
select 
    AGE, 
    CASE  
        WHEN GENDER_CODE = '20' THEN 'MALE' 
        ELSE 'FEMALE' 
    END AS GENDER, 
    ROUND(AVG(AVERAGE_VALUE),1) AS W_AVG,
    COUNT(PF_CODE) from SCHOOL_HEALTH
where SURVEY_YEAR  = 2019 and CATEGORY_CODE = '20'
group by GENDER_CODE, AGE) T2 
where T1.AGE = T2.AGE and T1.GENDER = T2.GENDER
order by T1.AGE desc,  T1.GENDER asc
) as T3 on SH.AGE = T3.AGE and GENDER = T3.GENDER and SH.AVERAGE_VALUE >= T3.H_AVG and SH.SURVEY_YEAR  = 2019 and SH.CATEGORY_CODE = '10'
group by  SH.AGE, GENDER
--) T4