mysql - Unable to create a sub-query

Question

If I have data below:

# EducationalAttainment, age, sum(population_count)
'Bachelor's degree or higher', '00 to 17', '14673'
'Bachelor's degree or higher', '18 to 64', '46032757'
'Bachelor's degree or higher', '65 to 80+', '8570246'
'High school or equivalent', '00 to 17', '114881'
'High school or equivalent', '18 to 64', '35577621'
'High school or equivalent', '65 to 80+', '7250424'
'No high school diploma', '00 to 17', '9566523'
'No high school diploma', '18 to 64', '25353234'
'No high school diploma', '65 to 80+', '5749114'
'Some college, less than 4-yr degree', '00 to 17', '91542'
'Some college, less than 4-yr degree', '18 to 64', '44843283'
'Some college, less than 4-yr degree', '65 to 80+', '7202669'

What kind of query would I write that adds population for each age group and calculate a fraction for instance those with bachelor's degree from 00 to 17 out total population of those 00 to 17.

This is from the following query:

select EducationalAttainment, age, sum(population_count) 
from educational_attainment
group by EducationalAttainment, age;

Answer 1

You would have to use a subquery with join:

SELECT A.EducationalAttainment, A.age, A.population_count/B.age_population_count population_fraction
FROM 
    educational_attainment A
JOIN
    (SELECT age, sum(population_count) age_population_count
    FROM educational_attainment
    GROUP BY age) B
ON A.age=B.age;