schools: gender_by_year_for_state_and_grade

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 enrollments
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 enrollments
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 enrollments
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 enrollments
where state = :state
and grade = :grade
and term = :term) select * from combined order by percent_change

Query parameters

state

grade

term

Edit SQL