schools

Data source: Big Local News · About: Big Local

Custom SQL query returning 4 rows (hide)

with combined as ( select 'female_int' as column, sum(female_int) filter (where year = 2015) as total_2015, sum(female_int) filter (where year = 2016) as total_2016, sum(female_int) filter (where year = 2017) as total_2017, sum(female_int) filter (where year = 2018) as total_2018, sum(female_int) filter (where year = 2019) as total_2019, sum(female_int) filter (where year = 2020) as total_2020, sum(female_int) filter (where year = 2021) as total_2021, ( sum(female_int) filter (where year = 2021)) - (sum(female_int) filter (where year = 2020) ) as change_2020_2021, round( (1.0 * ( sum(female_int) filter (where year = 2021)) - (sum(female_int) filter (where year = 2020))) / sum(female_int) filter (where year = 2020) * 100, 2) as percent_change from districts where state = :state and grade = :grade and term = :term union select 'male_int' as column, sum(male_int) filter (where year = 2015) as total_2015, sum(male_int) filter (where year = 2016) as total_2016, sum(male_int) filter (where year = 2017) as total_2017, sum(male_int) filter (where year = 2018) as total_2018, sum(male_int) filter (where year = 2019) as total_2019, sum(male_int) filter (where year = 2020) as total_2020, sum(male_int) filter (where year = 2021) as total_2021, ( sum(male_int) filter (where year = 2021)) - (sum(male_int) filter (where year = 2020) ) as change_2020_2021, round( (1.0 * ( sum(male_int) filter (where year = 2021)) - (sum(male_int) filter (where year = 2020))) / sum(male_int) filter (where year = 2020) * 100, 2) as percent_change from districts where state = :state and grade = :grade and term = :term union select 'non_binary_int' as column, sum(non_binary_int) filter (where year = 2015) as total_2015, sum(non_binary_int) filter (where year = 2016) as total_2016, sum(non_binary_int) filter (where year = 2017) as total_2017, sum(non_binary_int) filter (where year = 2018) as total_2018, sum(non_binary_int) filter (where year = 2019) as total_2019, sum(non_binary_int) filter (where year = 2020) as total_2020, sum(non_binary_int) filter (where year = 2021) as total_2021, ( sum(non_binary_int) filter (where year = 2021)) - (sum(non_binary_int) filter (where year = 2020) ) as change_2020_2021, round( (1.0 * ( sum(non_binary_int) filter (where year = 2021)) - (sum(non_binary_int) filter (where year = 2020))) / sum(non_binary_int) filter (where year = 2020) * 100, 2) as percent_change from districts where state = :state and grade = :grade and term = :term union select 'unknown_gender_int' as column, sum(unknown_gender_int) filter (where year = 2015) as total_2015, sum(unknown_gender_int) filter (where year = 2016) as total_2016, sum(unknown_gender_int) filter (where year = 2017) as total_2017, sum(unknown_gender_int) filter (where year = 2018) as total_2018, sum(unknown_gender_int) filter (where year = 2019) as total_2019, sum(unknown_gender_int) filter (where year = 2020) as total_2020, sum(unknown_gender_int) filter (where year = 2021) as total_2021, ( sum(unknown_gender_int) filter (where year = 2021)) - (sum(unknown_gender_int) filter (where year = 2020) ) as change_2020_2021, round( (1.0 * ( sum(unknown_gender_int) filter (where year = 2021)) - (sum(unknown_gender_int) filter (where year = 2020))) / sum(unknown_gender_int) filter (where year = 2020) * 100, 2) as percent_change from districts where state = :state and grade = :grade and term = :term) select * from combined order by percent_change

Query parameters

state

grade

term

This data as json, CSV

column	total_2015	total_2016	total_2017	total_2018	total_2019	total_2020	total_2021	change_2020_2021	percent_change
female_int
male_int
non_binary_int
unknown_gender_int