Database name : ca_pop Table name : educational_attainment Columns : Year | Age
ID: 3920775 • Letter: D
Question
Database name: ca_pop
Table name: educational_attainment
Columns: Year | Age | Gender | Educational Attainment | Personal Income | Population Count
Table name: pop_proj
Columns: fips | county | year | age | pop_female | pop_male | pop_total |
I wrote the following query to find out the total population in each age group from pop_proj.
?(Step 6 query) select pop_total, age
from pop_proj
group by age
Now i need a query to do the task below:
Use the query from Step 6 as a subquery to find each type of education attained by the population in that age group and the fraction of the population of that age group that has that educational attainment. Label the fraction column output as coefficient. For instance, the fraction of the population in age group 00 - 17 who has an education attainment of Bachelor's degree or higher is 0.0015, which is the coefficient. I'm using MySQL Workbench
Explanation / Answer
If you have any doubts, please give me comment...
SELECT Educational_Attainment, (SUM(Population_Count)/SUM(pop_total)) AS coefficient
FROM educational_attainment EA, (SELECT pop_total, age FROM pop_proj GROUP BY age) AS T
WHERE EA.Age = T.age
GROUP BY Educational_Attainment;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.