Use subquery and possible join to get population percentage

Question

I'm learning MySQL and I want to make two queries divide the sum(populationcount) values in each query to arrive at a percentage (as a decimal value) listed in a separate column. The goal is to use the first query that I will list below as a subquery to show the percentage of each type of education attained by each of the age groups.

The query that I need to use as the subquery is this: SELECT age,SUM(populationcount) FROM educational_attainment GROUP BY age; Population count results in only three different values, which are to be the divisor values in the new query.

The other query that I need to use values from is this: SELECT sum(populationcount), age, educationalattainment from educational_attainment group by age, educationalattainment The results here show 12 different values for populationcount, one for each level of education attained within each age group.

The query I have that is coming somewhat close looks something like this: SELECT A.EducationalAttainment, A.age, A.populationcount/B.age FROM educational_attainment A JOIN (SELECT age, sum(populationcount) age_populationcount FROM educational_attainment GROUP BY age) B ON A.age=B.age; Unfortunately, it's giving me way too many results for each age group and the calculated values are greater than 0 instead of a decimal value less than 0.

I hope I explained this well enough. If anyone has any insight on what I'm doing wrong, I would greatly appreciate your input! Thanks!

score 0 · Accepted Answer · answered Jul 26 '18 at 03:44

You should use a LEFT JOIN instead as illustrated below:

SELECT 
    A.age, B.educationalattainment, 
    (IFNULL(B.pop_count, 0)/A.pop_count)*100 percentage_pop
FROM
(SELECT 
    age, SUM(populationcount) pop_count 
 FROM educational_attainment 
 GROUP BY age) A
LEFT JOIN 
(SELECT 
    sum(populationcount) pop_count, age, educationalattainment 
 FROM educational_attainment 
 GROUP BY age, educationalattainment) B
ON A.age=B.age;

Use subquery and possible join to get population percentage

1 Answers1