--finds number of people for each occupation group with capital gain above average in their own group
SELECT  ifnull(person.occupation,'N/A') AS occupation, 
        COUNT(*) AS total_people, 			
        SUM(CASE WHEN capitalgain > avg_table.avggain THEN 1 ELSE 0 END) AS people_aboveaverage

FROM  ( SELECT ifnull(occupation,'N/A') AS occupation, 
                AVG(capitalgain) AS avggain
        FROM person 			
        GROUP BY occupation) as avg_table,
        person 
WHERE
        person.occupation = avg_table.occupation

GROUP BY person.occupation
ORDER BY 3 DESC;