CREATE TABLE female_distinct
SELECT distinct
kenya_grades.`g_knec_code`,
kenya_grades.`g_grade_attained`,
kenya_grades.`g_frequency` AS freq_a_f,
kenya_grades.`g_frequency` AS freq_aminus_f,
kenya_grades.`g_frequency` AS freq_bplus_f,
kenya_grades.`g_frequency` AS freq_b_f,
kenya_grades.`g_frequency` AS freq_bminus_f,
kenya_grades.`g_frequency` AS freq_cplus_f,
kenya_grades.`g_frequency` AS freq_c_f,
kenya_grades.`g_frequency` AS freq_cminus_f,
kenya_grades.`g_frequency` AS freq_dplus_f,
kenya_grades.`g_frequency` AS freq_d_f,
kenya_grades.`g_frequency` AS freq_dminus_f,
kenya_grades.`g_frequency` AS freq_e_f
FROM
kenya_grades JOIN gender_distinct ON gender_distinct.`g_knec_code` = kenya_grades.`g_knec_code`
WHERE
kenya_grades.`g_year` = 2010 AND kenya_grades.`g_gender` = "F" AND gender_distinct.`g_knec_code` IS NOT NULL AND kenya_grades.`g_knec_code` IS NOT NULL AND kenya_grades.`g_grade_attained` = gender_distinct.`g_grade_attained` AND gender_distinct.`g_knec_code` = kenya_grades.`g_knec_code`;
CREATE TABLE male_distinct
SELECT DISTINCT
kenya_grades.`g_knec_code`,
kenya_grades.`g_grade_attained`,
kenya_grades.`g_frequency` AS freq_a_m,
kenya_grades.`g_frequency` AS freq_aminus_m,
kenya_grades.`g_frequency` AS freq_bplus_m,
kenya_grades.`g_frequency` AS freq_b_m,
kenya_grades.`g_frequency` AS freq_bminus_m,
kenya_grades.`g_frequency` AS freq_cplus_m,
kenya_grades.`g_frequency` AS freq_c_m,
kenya_grades.`g_frequency` AS freq_cminus_m,
kenya_grades.`g_frequency` AS freq_dplus_m,
kenya_grades.`g_frequency` AS freq_d_m,
kenya_grades.`g_frequency` AS freq_dminus_m,
kenya_grades.`g_frequency` AS freq_e_m
FROM
kenya_grades JOIN gender_distinct ON gender_distinct.`g_knec_code` = kenya_grades.`g_knec_code`
WHERE
kenya_grades.`g_year` = 2010 AND kenya_grades.`g_gender` = "M" AND gender_distinct.`g_knec_code` IS NOT NULL AND kenya_grades.`g_knec_code` IS NOT NULL AND kenya_grades.`g_grade_attained` = gender_distinct.`g_grade_attained` AND gender_distinct.`g_knec_code` = kenya_grades.`g_knec_code`;
CREATE TABLE gender_distinct
SELECT
female_distinct.`g_knec_code`,
female_distinct.`g_grade_attained`,
female_distinct.`freq_a_f`,
female_distinct.`freq_aminus_f`,
female_distinct.`freq_bplus_f`,
female_distinct.`freq_b_f`,
female_distinct.`freq_bminus_f`,
female_distinct.`freq_cplus_f`,
female_distinct.`freq_c_f`,
female_distinct.`freq_cminus_f`,
female_distinct.`freq_dplus_f`,
female_distinct.`freq_d_f`,
female_distinct.`freq_dminus_f`,
female_distinct.`freq_e_f`,
male_distinct.`freq_a_m`,
male_distinct.`freq_aminus_m`,
male_distinct.`freq_bplus_m`,
male_distinct.`freq_b_m`,
male_distinct.`freq_bminus_m`,
male_distinct.`freq_cplus_m`,
male_distinct.`freq_c_m`,
male_distinct.`freq_cminus_m`,
male_distinct.`freq_dplus_m`,
male_distinct.`freq_d_m`,
male_distinct.`freq_dminus_m`,
male_distinct.`freq_e_m`
from male_distinct LEFT JOIN female_distinct ON (male_distinct.`g_knec_code` = female_distinct.`g_knec_code` AND female_distinct.`g_grade_attained` = male_distinct.`g_grade_attained`)
WHERE male_distinct.`g_knec_code` IS NOT NULL AND female_distinct.`g_knec_code` IS NOT NULL;
Update gender_distinct
set gender_distinct.`freq_aminus_m`='' where gender_distinct.`g_grade_attained` != "A-";
Update gender_distinct
set gender_distinct.`freq_aminus_f`='' where gender_distinct.`g_grade_attained` != "A-";
Update gender_distinct
set gender_distinct.`freq_a_m`='' where gender_distinct.`g_grade_attained` != "A";
Update gender_distinct
set gender_distinct.`freq_a_f`='' where gender_distinct.`g_grade_attained` != "A";
Update gender_distinct
set gender_distinct.`freq_bplus_m`='' where gender_distinct.`g_grade_attained` != "B+";
Update gender_distinct
set gender_distinct.`freq_bplus_f`='' where gender_distinct.`g_grade_attained` != "B+";
Update gender_distinct
set gender_distinct.`freq_b_m`='' where gender_distinct.`g_grade_attained` != "B";
Update gender_distinct
set gender_distinct.`freq_b_f`='' where gender_distinct.`g_grade_attained` != "B";
Update gender_distinct
set gender_distinct.`freq_bminus_m`='' where gender_distinct.`g_grade_attained` != "B-";
Update gender_distinct
set gender_distinct.`freq_bminus_f`='' where gender_distinct.`g_grade_attained` != "B-";
Update gender_distinct
set gender_distinct.`freq_cplus_m`='' where gender_distinct.`g_grade_attained` != "C+";
Update gender_distinct
set gender_distinct.`freq_cplus_f`='' where gender_distinct.`g_grade_attained` != "C+";
Update gender_distinct
set gender_distinct.`freq_c_m`='' where gender_distinct.`g_grade_attained` != "C";
Update gender_distinct
set gender_distinct.`freq_c_f`='' where gender_distinct.`g_grade_attained` != "C";
Update gender_distinct
set gender_distinct.`freq_cminus_m`='' where gender_distinct.`g_grade_attained` != "C-";
Update gender_distinct
set gender_distinct.`freq_cminus_f`='' where gender_distinct.`g_grade_attained` != "C-";
Update gender_distinct
set gender_distinct.`freq_dplus_m`='' where gender_distinct.`g_grade_attained` != "D+";
Update gender_distinct
set gender_distinct.`freq_dplus_f`='' where gender_distinct.`g_grade_attained` != "D+";
Update gender_distinct
set gender_distinct.`freq_d_m`='' where gender_distinct.`g_grade_attained` != "D";
Update gender_distinct
set gender_distinct.`freq_d_f`='' where gender_distinct.`g_grade_attained` != "D";
Update gender_distinct
set gender_distinct.`freq_dminus_m`='' where gender_distinct.`g_grade_attained` != "D-";
Update gender_distinct
set gender_distinct.`freq_dminus_f`='' where gender_distinct.`g_grade_attained` != "D-";
Update gender_distinct
set gender_distinct.`freq_dminus_m`='' where gender_distinct.`g_grade_attained` != "D-";
Update gender_distinct
set gender_distinct.`freq_dminus_f`='' where gender_distinct.`g_grade_attained` != "D-";
Update gender_distinct
set gender_distinct.`freq_e_m`='' where gender_distinct.`g_grade_attained` != "E";
Update gender_distinct
set gender_distinct.`freq_e_f`='' where gender_distinct.`g_grade_attained` != "E";
Create table school_summary_freq
select
gender_distinct.`g_knec_code`,
sum(gender_distinct.`freq_a_f`) as sum_freq_a_f,
sum(gender_distinct.`freq_aminus_f`) as sum_freq_aminus_f,
sum(gender_distinct.`freq_bplus_f`) as sum_freq_bplus_f,
sum(gender_distinct.`freq_b_f`) as sum_freq_b_f,
sum(gender_distinct.`freq_bminus_f`) as sum_freq_bminus_f,
sum(gender_distinct.`freq_cplus_f`) as sum_freq_cplus_f,
sum(gender_distinct.`freq_c_f`) as sum_freq_c_f,
sum(gender_distinct.`freq_cminus_f`) as sum_freq_cminus_f,
sum(gender_distinct.`freq_dplus_f`) as sum_freq_dplus_f,
sum(gender_distinct.`freq_d_f`) as sum_freq_d_f,
sum(gender_distinct.`freq_dminus_f`) as sum_freq_dminus_f,
sum(gender_distinct.`freq_e_f`) as sum_freq_e_f,
sum(gender_distinct.`freq_a_m`) as sum_freq_a_m,
sum(gender_distinct.`freq_aminus_m`) as sum_freq_aminus_m,
sum(gender_distinct.`freq_bplus_m`) as sum_freq_bplus_m,
sum(gender_distinct.`freq_b_m`) as sum_freq_b_m,
sum(gender_distinct.`freq_bminus_m`) as sum_freq_bminus_m,
sum(gender_distinct.`freq_cplus_m`) as sum_freq_cplus_m,
sum(gender_distinct.`freq_c_m`) as sum_freq_c_m,
sum(gender_distinct.`freq_cminus_m`) as sum_freq_cminus_m,
sum(gender_distinct.`freq_dplus_m`) as sum_freq_dplus_m,
sum(gender_distinct.`freq_d_m`) as sum_freq_d_m,
sum(gender_distinct.`freq_dminus_m`) as sum_freq_dminus_m,
sum(gender_distinct.`freq_e_m`) as sum_freq_e_m,
school_pop_size.`school_population`,
school_pop_size.`male_students_total`,
school_pop_size.`female_students_total`
from gender_distinct, school_pop_size
WHERE school_pop_size.`g_knec_code` = gender_distinct.`g_knec_code`
group by g_knec_code;
SELECT
school_summary_freq.`g_knec_code`,
((school_summary_freq.`sum_freq_a_f` + school_summary_freq.`sum_freq_aminus_f`) / school_summary_freq.`female_students_total`) AS pct_female_students_a,
round(((school_summary_freq.`sum_freq_bplus_f` +
school_summary_freq.`sum_freq_b_f` +
school_summary_freq.`sum_freq_bminus_f`) / school_summary_freq.`female_students_total`),0) AS pct_female_students_b,
round(((school_summary_freq.`sum_freq_cplus_f` +
school_summary_freq.`sum_freq_c_f` +
school_summary_freq.`sum_freq_cminus_f` ) / school_summary_freq.`female_students_total`),0) AS pct_female_students_c,
round(((school_summary_freq.`sum_freq_dplus_f` +
school_summary_freq.`sum_freq_d_f` + school_summary_freq.`sum_freq_dminus_f`) / school_summary_freq.`female_students_total`),0) AS pct_female_students_d,
round(((school_summary_freq.`sum_freq_e_f`) / school_summary_freq.`female_students_total`),0) AS pct_female_students_e,
round(((school_summary_freq.`sum_freq_a_m` + school_summary_freq.`sum_freq_aminus_m`) / school_summary_freq.`male_students_total`),0) AS pct_male_students_a,
round(((school_summary_freq.`sum_freq_bplus_m` + school_summary_freq.`sum_freq_b_m` + school_summary_freq.`sum_freq_bminus_m`) / school_summary_freq.`male_students_total`),0) AS pct_male_students_b,
round(((school_summary_freq.`sum_freq_cplus_m` + school_summary_freq.`sum_freq_c_m` + school_summary_freq.`sum_freq_cminus_m`) / school_summary_freq.`male_students_total`),0) AS pct_male_students_c,
round(((school_summary_freq.`sum_freq_dplus_m` + school_summary_freq.`sum_freq_d_m` + school_summary_freq.`sum_freq_dminus_m`) / school_summary_freq.`male_students_total`),0) AS pct_male_students_d,
round(((school_summary_freq.`sum_freq_e_m`)
/ school_summary_freq.`male_students_total`),0) AS pct_male_students_e,
round(((school_summary_freq.`sum_freq_a_f` + school_summary_freq.`sum_freq_aminus_f` + school_summary_freq.`sum_freq_bplus_f` + school_summary_freq.`sum_freq_b_f` + school_summary_freq.`sum_freq_bminus_f` + school_summary_freq.`sum_freq_cplus_f`+ school_summary_freq.`sum_freq_a_m` + school_summary_freq.`sum_freq_aminus_m` + school_summary_freq.`sum_freq_bplus_m` + school_summary_freq.`sum_freq_b_m` + school_summary_freq.`sum_freq_bminus_m` + school_summary_freq.`sum_freq_cplus_m`) / school_summary_freq.`school_population`),0) AS pct_tot_all_above_cplus,
round(((school_summary_freq.`sum_freq_c_f` + school_summary_freq.`sum_freq_cminus_f` + school_summary_freq.`sum_freq_dplus_f` + school_summary_freq.`sum_freq_d_f` + school_summary_freq.`sum_freq_dminus_f` + school_summary_freq.`sum_freq_e_f` + + school_summary_freq.`sum_freq_c_m` + school_summary_freq.`sum_freq_cminus_m` + school_summary_freq.`sum_freq_dplus_m` + school_summary_freq.`sum_freq_d_m` + school_summary_freq.`sum_freq_dminus_m` + school_summary_freq.`sum_freq_e_m`) / school_summary_freq.`school_population`),0) AS pct_tot_all_below_cplus,
school_summary_freq.`school_population`,
school_summary_freq.`male_students_total`,
school_summary_freq.`female_students_total`,
school_locations.`latitude`,
school_locations.`longitude`,
LOWER(school_locations.`g_school_name`) AS g_school_name_low,
LOWER(school_locations.`s_name_of_school`) AS s_name_of_school_low,
gjoined.`g_county`,
gjoined.`school_sponsor`
FROM school_locations, school_summary_freq, gjoined
WHERE gjoined.`g_year` = 2010 AND gjoined.`g_knec_code` = school_summary_freq.`g_knec_code` = gjoined.`g_knec_code`
INTO OUTFILE "~/desktop/kenya_join.csv";
Notes: KNEC_code 205, 401, 508, 557, and 805 were removed due to knec code not being a unique identifier for the schoool In the case of 557, it appears that county code and knec code were used interchangably, resulting in inaccurate summary statistics based on the school via the knec groupings.
This resulted in unusable percentage distributions for grades.
- Largely resolved due to recalculation of total number of test takers based on KNEC code, rather than school name, as the grade frequency summations were grouped by KNEC code.
- This means that some schools may not come up under their school name, but rather as part of the KNEC code grouping.
CREATE TABLE sum_loc_freq
SELECT
school_locations_summary.`g_knec_code`,
LOWER(school_locations_summary.`g_county`) AS g_county,
LOWER(school_locations_summary.`g_school_name`) AS g_school_name,
LOWER(school_locations_summary.`s_name_of_school`) AS s_name_of_school,
LOWER(school_locations_summary.`school_sponsor`) AS school_sponsor,
school_locations_summary.`latitude`,
school_locations_summary.`longitude`,
CONVERT(school_summary_freq.`sum_freq_a_f`, UNSIGNED) AS sum_freq_a_f,
CONVERT(school_summary_freq.`sum_freq_aminus_f`, UNSIGNED) AS sum_freq_aminus_f,
CONVERT(school_summary_freq.`sum_freq_bplus_f`, UNSIGNED) AS sum_freq_bplus_f,
CONVERT(school_summary_freq.`sum_freq_b_f`, UNSIGNED) AS sum_freq_b_f,
CONVERT(school_summary_freq.`sum_freq_bminus_f`, UNSIGNED) AS sum_freq_bminus_f,
CONVERT(school_summary_freq.`sum_freq_cplus_f`, UNSIGNED) AS sum_freq_cplus_f,
CONVERT(school_summary_freq.`sum_freq_c_f`, UNSIGNED) AS sum_freq_c_f,
CONVERT(school_summary_freq.`sum_freq_cminus_f`, UNSIGNED) AS sum_freq_cminus_f,
CONVERT(school_summary_freq.`sum_freq_dplus_f`, UNSIGNED) AS sum_freq_dplus_f,
CONVERT(school_summary_freq.`sum_freq_d_f`, UNSIGNED) AS sum_freq_d_f,
CONVERT(school_summary_freq.`sum_freq_dminus_f`, UNSIGNED) AS sum_freq_dminus_f,
CONVERT(school_summary_freq.`sum_freq_e_f`, UNSIGNED) AS sum_freq_e_f,
CONVERT(school_summary_freq.`sum_freq_a_m`, UNSIGNED) AS sum_freq_a_m,
CONVERT(school_summary_freq.`sum_freq_aminus_m`, UNSIGNED) AS sum_freq_aminus_m,
CONVERT(school_summary_freq.`sum_freq_bplus_m`, UNSIGNED) AS sum_freq_bplus_m,
CONVERT(school_summary_freq.`sum_freq_b_m`, UNSIGNED) AS sum_freq_b_m,
CONVERT(school_summary_freq.`sum_freq_bminus_m`, UNSIGNED) AS sum_freq_bminus_m,
CONVERT(school_summary_freq.`sum_freq_cplus_m`, UNSIGNED) AS sum_freq_cplus_m,
CONVERT(school_summary_freq.`sum_freq_c_m`, UNSIGNED) AS sum_freq_c_m,
CONVERT(school_summary_freq.`sum_freq_cminus_m`, UNSIGNED) AS sum_freq_cminus_m,
CONVERT(school_summary_freq.`sum_freq_dplus_m`, UNSIGNED) AS sum_freq_dplus_m,
CONVERT(school_summary_freq.`sum_freq_d_m`, UNSIGNED) AS sum_freq_d_m,
CONVERT(school_summary_freq.`sum_freq_dminus_m`, UNSIGNED) AS sum_freq_dminus_m,
CONVERT(school_summary_freq.`sum_freq_e_m`, UNSIGNED) AS sum_freq_e_m
FROM
school_locations_summary LEFT JOIN school_summary_freq
ON
school_locations_summary.`g_knec_code` = school_summary_freq.`g_knec_code`
AND
school_locations_summary.`latitude` IS NOT NULL;
CREATE TABLE final_knec_score_summary
SELECT
g_knec_code,
ROUND(((sum_freq_a_f +
sum_freq_aminus_f) / (sum_freq_a_f +
sum_freq_aminus_f +
sum_freq_bplus_f +
sum_freq_b_f +
sum_freq_bminus_f +
sum_freq_cplus_f +
sum_freq_c_f +
sum_freq_cminus_f +
sum_freq_dplus_f +
sum_freq_d_f +
sum_freq_dminus_f +
sum_freq_e_f)*100),0) AS pct_female_students_a,
ROUND(((sum_freq_bplus_f +
sum_freq_b_f +
sum_freq_bminus_f) / (sum_freq_a_f +
sum_freq_aminus_f +
sum_freq_bplus_f +
sum_freq_b_f +
sum_freq_bminus_f +
sum_freq_cplus_f +
sum_freq_c_f +
sum_freq_cminus_f +
sum_freq_dplus_f +
sum_freq_d_f +
sum_freq_dminus_f +
sum_freq_e_f)*100),0) AS pct_female_students_b,
ROUND(((sum_freq_cplus_f +
sum_freq_c_f +
sum_freq_cminus_f ) / (sum_freq_a_f +
sum_freq_aminus_f +
sum_freq_bplus_f +
sum_freq_b_f +
sum_freq_bminus_f +
sum_freq_cplus_f +
sum_freq_c_f +
sum_freq_cminus_f +
sum_freq_dplus_f +
sum_freq_d_f +
sum_freq_dminus_f +
sum_freq_e_f)*100),0) AS pct_female_students_c,
ROUND(((sum_freq_dplus_f +
sum_freq_d_f + sum_freq_dminus_f) / (sum_freq_a_f +
sum_freq_aminus_f +
sum_freq_bplus_f +
sum_freq_b_f +
sum_freq_bminus_f +
sum_freq_cplus_f +
sum_freq_c_f +
sum_freq_cminus_f +
sum_freq_dplus_f +
sum_freq_d_f +
sum_freq_dminus_f +
sum_freq_e_f)*100),0) AS pct_female_students_d,
ROUND(((sum_freq_e_f) / (sum_freq_a_f +
sum_freq_aminus_f +
sum_freq_bplus_f +
sum_freq_b_f +
sum_freq_bminus_f +
sum_freq_cplus_f +
sum_freq_c_f +
sum_freq_cminus_f +
sum_freq_dplus_f +
sum_freq_d_f +
sum_freq_dminus_f +
sum_freq_e_f)*100),0) AS pct_female_students_e,
ROUND(((sum_freq_a_m +
sum_freq_aminus_m) / (sum_freq_a_m +
sum_freq_aminus_m +
sum_freq_bplus_m +
sum_freq_b_m +
sum_freq_bminus_m +
sum_freq_cplus_m +
sum_freq_c_m +
sum_freq_cminus_m +
sum_freq_dplus_m +
sum_freq_d_m +
sum_freq_dminus_m +
sum_freq_e_m)*100),0) AS pct_male_students_a,
ROUND(((sum_freq_bplus_m +
sum_freq_b_m +
sum_freq_bminus_m) / (sum_freq_a_m +
sum_freq_aminus_m +
sum_freq_bplus_m +
sum_freq_b_m +
sum_freq_bminus_m +
sum_freq_cplus_m +
sum_freq_c_m +
sum_freq_cminus_m +
sum_freq_dplus_m +
sum_freq_d_m +
sum_freq_dminus_m +
sum_freq_e_m)*100),0) AS pct_male_students_b,
ROUND(((sum_freq_cplus_m +
sum_freq_c_m +
sum_freq_cminus_m) / (sum_freq_a_m +
sum_freq_aminus_m +
sum_freq_bplus_m +
sum_freq_b_m +
sum_freq_bminus_m +
sum_freq_cplus_m +
sum_freq_c_m +
sum_freq_cminus_m +
sum_freq_dplus_m +
sum_freq_d_m +
sum_freq_dminus_m +
sum_freq_e_m)*100),0) AS pct_male_students_c,
ROUND(((sum_freq_dplus_m +
sum_freq_d_m +
sum_freq_dminus_m) / (sum_freq_a_m +
sum_freq_aminus_m +
sum_freq_bplus_m +
sum_freq_b_m +
sum_freq_bminus_m +
sum_freq_cplus_m +
sum_freq_c_m +
sum_freq_cminus_m +
sum_freq_dplus_m +
sum_freq_d_m +
sum_freq_dminus_m +
sum_freq_e_m)*100),0)AS pct_male_students_d,
ROUND(((sum_freq_e_m)
/ (sum_freq_a_m +
sum_freq_aminus_m +
sum_freq_bplus_m +
sum_freq_b_m +
sum_freq_bminus_m +
sum_freq_cplus_m +
sum_freq_c_m +
sum_freq_cminus_m +
sum_freq_dplus_m +
sum_freq_d_m +
sum_freq_dminus_m +
sum_freq_e_m)*100),0) AS pct_male_students_e,
ROUND(((sum_freq_a_f +
sum_freq_aminus_f +
sum_freq_bplus_f +
sum_freq_b_f +
sum_freq_bminus_f +
sum_freq_cplus_f+
sum_freq_a_m +
sum_freq_aminus_m +
sum_freq_bplus_m +
sum_freq_b_m +
sum_freq_bminus_m +
sum_freq_cplus_m) / (sum_freq_a_f +
sum_freq_aminus_f +
sum_freq_bplus_f +
sum_freq_b_f +
sum_freq_bminus_f +
sum_freq_cplus_f +
sum_freq_c_f +
sum_freq_cminus_f +
sum_freq_dplus_f +
sum_freq_d_f +
sum_freq_dminus_f +
sum_freq_e_f +
sum_freq_a_m +
sum_freq_aminus_m +
sum_freq_bplus_m +
sum_freq_b_m +
sum_freq_bminus_m +
sum_freq_cplus_m +
sum_freq_c_m +
sum_freq_cminus_m +
sum_freq_dplus_m +
sum_freq_d_m +
sum_freq_dminus_m +
sum_freq_e_m)*100),0) AS knec_pct_tot_all_above_cplus,
ROUND(((sum_freq_c_f +
sum_freq_cminus_f +
sum_freq_dplus_f +
sum_freq_d_f +
sum_freq_dminus_f +
sum_freq_e_f +
sum_freq_c_m +
sum_freq_cminus_m +
sum_freq_dplus_m +
sum_freq_d_m +
sum_freq_dminus_m +
sum_freq_e_m) / (sum_freq_a_f +
sum_freq_aminus_f +
sum_freq_bplus_f +
sum_freq_b_f +
sum_freq_bminus_f +
sum_freq_cplus_f +
sum_freq_c_f +
sum_freq_cminus_f +
sum_freq_dplus_f +
sum_freq_d_f +
sum_freq_dminus_f +
sum_freq_e_f +
sum_freq_a_m +
sum_freq_aminus_m +
sum_freq_bplus_m +
sum_freq_b_m +
sum_freq_bminus_m +
sum_freq_cplus_m +
sum_freq_c_m +
sum_freq_cminus_m +
sum_freq_dplus_m +
sum_freq_d_m +
sum_freq_dminus_m +
sum_freq_e_m)*100),0) AS knec_pct_tot_all_below_cplus,
(sum_freq_a_m +
sum_freq_aminus_m +
sum_freq_bplus_m +
sum_freq_b_m +
sum_freq_bminus_m +
sum_freq_cplus_m +
sum_freq_c_m +
sum_freq_cminus_m +
sum_freq_dplus_m +
sum_freq_d_m +
sum_freq_dminus_m +
sum_freq_e_m) AS knec_total_male_students,
(sum_freq_a_f +
sum_freq_aminus_f +
sum_freq_bplus_f +
sum_freq_b_f +
sum_freq_bminus_f +
sum_freq_cplus_f +
sum_freq_c_f +
sum_freq_cminus_f +
sum_freq_dplus_f +
sum_freq_d_f +
sum_freq_dminus_f +
sum_freq_e_f) AS knec_total_female_students,
(sum_freq_a_f +
sum_freq_aminus_f +
sum_freq_bplus_f +
sum_freq_b_f +
sum_freq_bminus_f +
sum_freq_cplus_f +
sum_freq_c_f +
sum_freq_cminus_f +
sum_freq_dplus_f +
sum_freq_d_f +
sum_freq_dminus_f +
sum_freq_e_f +
sum_freq_a_m +
sum_freq_aminus_m +
sum_freq_bplus_m +
sum_freq_b_m +
sum_freq_bminus_m +
sum_freq_cplus_m +
sum_freq_c_m +
sum_freq_cminus_m +
sum_freq_dplus_m +
sum_freq_d_m +
sum_freq_dminus_m +
sum_freq_e_m) AS knec_total_students,
g_school_name,
s_name_of_school,
latitude,
longitude,
g_county,
school_sponsor
FROM sum_loc_freq;