DELETE FROM SCHOOL_HEALTH WHERE SURVEY_YEAR != 2019;
SELECT X.AGE,CASE X.GENDER_CODE WHEN "20" THEN "MALE" ELSE "FEMALE" END GENDER,
ROUND((SELECT AVG(H.AVERAGE_VALUE) FROM SCHOOL_HEALTH AS H WHERE H.CATEGORY_CODE == "10" AND H.GENDER_CODE == X.GENDER_CODE AND H.AGE == X.AGE),1) AS H_AVG,
ROUND(100.0 * (SELECT COUNT(H2.AVERAGE_VALUE) FROM SCHOOL_HEALTH AS H2 WHERE H2.CATEGORY_CODE == "10" AND H2.GENDER_CODE == X.GENDER_CODE AND H2.AGE == X.AGE AND H2.AVERAGE_VALUE >= ROUND((SELECT AVG(H.AVERAGE_VALUE) FROM SCHOOL_HEALTH AS H WHERE H.CATEGORY_CODE == "10" AND H.GENDER_CODE == X.GENDER_CODE AND H.AGE == X.AGE),1)) / (SELECT COUNT(H.AVERAGE_VALUE) FROM SCHOOL_HEALTH AS H WHERE H.CATEGORY_CODE == "10" AND H.GENDER_CODE == X.GENDER_CODE AND H.AGE == X.AGE),1) || "%" AS H_PER,
ROUND((SELECT AVG(H.AVERAGE_VALUE) FROM SCHOOL_HEALTH AS H WHERE H.CATEGORY_CODE == "20" AND H.GENDER_CODE == X.GENDER_CODE AND H.AGE == X.AGE),1) AS W_AVG,
ROUND(100.0 * (SELECT COUNT(H2.AVERAGE_VALUE) FROM SCHOOL_HEALTH AS H2 WHERE H2.CATEGORY_CODE == "20" AND H2.GENDER_CODE == X.GENDER_CODE AND H2.AGE == X.AGE AND H2.AVERAGE_VALUE >= ROUND((SELECT AVG(H.AVERAGE_VALUE) FROM SCHOOL_HEALTH AS H WHERE H.CATEGORY_CODE == "20" AND H.GENDER_CODE == X.GENDER_CODE AND H.AGE == X.AGE),1)) / (SELECT COUNT(H.AVERAGE_VALUE) FROM SCHOOL_HEALTH AS H WHERE H.CATEGORY_CODE == "20" AND H.GENDER_CODE == X.GENDER_CODE AND H.AGE == X.AGE),1) || "%" AS W_PER
FROM SCHOOL_HEALTH AS X
GROUP BY X.AGE,X.GENDER_CODE ORDER BY X.AGE DESC,X.GENDER_CODE DESC;