schools: district_race_ethnicity_by_year_for_state_and_grade

Custom SQL query returning 12 rows (hide)

with combined as (
select
  'asian_int' as column,
  sum(asian_int) filter (where year = 2015) as total_2015,
  sum(asian_int) filter (where year = 2016) as total_2016,
  sum(asian_int) filter (where year = 2017) as total_2017,
  sum(asian_int) filter (where year = 2018) as total_2018,
  sum(asian_int) filter (where year = 2019) as total_2019,
  sum(asian_int) filter (where year = 2020) as total_2020,
  sum(asian_int) filter (where year = 2021) as total_2021,
  (
    sum(asian_int) filter (where year = 2021)) - (sum(asian_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(asian_int) filter (where year = 2021)) - (sum(asian_int) filter (where year = 2020))) /
      sum(asian_int) filter (where year = 2020) * 100,
  2) as percent_change
from districts
where state = :state
and grade = :grade
and term = :term
union
select
  'asian_pacific_islander_int' as column,
  sum(asian_pacific_islander_int) filter (where year = 2015) as total_2015,
  sum(asian_pacific_islander_int) filter (where year = 2016) as total_2016,
  sum(asian_pacific_islander_int) filter (where year = 2017) as total_2017,
  sum(asian_pacific_islander_int) filter (where year = 2018) as total_2018,
  sum(asian_pacific_islander_int) filter (where year = 2019) as total_2019,
  sum(asian_pacific_islander_int) filter (where year = 2020) as total_2020,
  sum(asian_pacific_islander_int) filter (where year = 2021) as total_2021,
  (
    sum(asian_pacific_islander_int) filter (where year = 2021)) - (sum(asian_pacific_islander_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(asian_pacific_islander_int) filter (where year = 2021)) - (sum(asian_pacific_islander_int) filter (where year = 2020))) /
      sum(asian_pacific_islander_int) filter (where year = 2020) * 100,
  2) as percent_change
from districts
where state = :state
and grade = :grade
and term = :term
union
select
  'black_int' as column,
  sum(black_int) filter (where year = 2015) as total_2015,
  sum(black_int) filter (where year = 2016) as total_2016,
  sum(black_int) filter (where year = 2017) as total_2017,
  sum(black_int) filter (where year = 2018) as total_2018,
  sum(black_int) filter (where year = 2019) as total_2019,
  sum(black_int) filter (where year = 2020) as total_2020,
  sum(black_int) filter (where year = 2021) as total_2021,
  (
    sum(black_int) filter (where year = 2021)) - (sum(black_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(black_int) filter (where year = 2021)) - (sum(black_int) filter (where year = 2020))) /
      sum(black_int) filter (where year = 2020) * 100,
  2) as percent_change
from districts
where state = :state
and grade = :grade
and term = :term
union
select
  'hispanic_int' as column,
  sum(hispanic_int) filter (where year = 2015) as total_2015,
  sum(hispanic_int) filter (where year = 2016) as total_2016,
  sum(hispanic_int) filter (where year = 2017) as total_2017,
  sum(hispanic_int) filter (where year = 2018) as total_2018,
  sum(hispanic_int) filter (where year = 2019) as total_2019,
  sum(hispanic_int) filter (where year = 2020) as total_2020,
  sum(hispanic_int) filter (where year = 2021) as total_2021,
  (
    sum(hispanic_int) filter (where year = 2021)) - (sum(hispanic_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(hispanic_int) filter (where year = 2021)) - (sum(hispanic_int) filter (where year = 2020))) /
      sum(hispanic_int) filter (where year = 2020) * 100,
  2) as percent_change
from districts
where state = :state
and grade = :grade
and term = :term
union
select
  'multiracial_int' as column,
  sum(multiracial_int) filter (where year = 2015) as total_2015,
  sum(multiracial_int) filter (where year = 2016) as total_2016,
  sum(multiracial_int) filter (where year = 2017) as total_2017,
  sum(multiracial_int) filter (where year = 2018) as total_2018,
  sum(multiracial_int) filter (where year = 2019) as total_2019,
  sum(multiracial_int) filter (where year = 2020) as total_2020,
  sum(multiracial_int) filter (where year = 2021) as total_2021,
  (
    sum(multiracial_int) filter (where year = 2021)) - (sum(multiracial_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(multiracial_int) filter (where year = 2021)) - (sum(multiracial_int) filter (where year = 2020))) /
      sum(multiracial_int) filter (where year = 2020) * 100,
  2) as percent_change
from districts
where state = :state
and grade = :grade
and term = :term
union
select
  'native_american_alaskan_native_int' as column,
  sum(native_american_alaskan_native_int) filter (where year = 2015) as total_2015,
  sum(native_american_alaskan_native_int) filter (where year = 2016) as total_2016,
  sum(native_american_alaskan_native_int) filter (where year = 2017) as total_2017,
  sum(native_american_alaskan_native_int) filter (where year = 2018) as total_2018,
  sum(native_american_alaskan_native_int) filter (where year = 2019) as total_2019,
  sum(native_american_alaskan_native_int) filter (where year = 2020) as total_2020,
  sum(native_american_alaskan_native_int) filter (where year = 2021) as total_2021,
  (
    sum(native_american_alaskan_native_int) filter (where year = 2021)) - (sum(native_american_alaskan_native_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(native_american_alaskan_native_int) filter (where year = 2021)) - (sum(native_american_alaskan_native_int) filter (where year = 2020))) /
      sum(native_american_alaskan_native_int) filter (where year = 2020) * 100,
  2) as percent_change
from districts
where state = :state
and grade = :grade
and term = :term
union
select
  'native_american_int' as column,
  sum(native_american_int) filter (where year = 2015) as total_2015,
  sum(native_american_int) filter (where year = 2016) as total_2016,
  sum(native_american_int) filter (where year = 2017) as total_2017,
  sum(native_american_int) filter (where year = 2018) as total_2018,
  sum(native_american_int) filter (where year = 2019) as total_2019,
  sum(native_american_int) filter (where year = 2020) as total_2020,
  sum(native_american_int) filter (where year = 2021) as total_2021,
  (
    sum(native_american_int) filter (where year = 2021)) - (sum(native_american_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(native_american_int) filter (where year = 2021)) - (sum(native_american_int) filter (where year = 2020))) /
      sum(native_american_int) filter (where year = 2020) * 100,
  2) as percent_change
from districts
where state = :state
and grade = :grade
and term = :term
union
select
  'native_hawaiian_int' as column,
  sum(native_hawaiian_int) filter (where year = 2015) as total_2015,
  sum(native_hawaiian_int) filter (where year = 2016) as total_2016,
  sum(native_hawaiian_int) filter (where year = 2017) as total_2017,
  sum(native_hawaiian_int) filter (where year = 2018) as total_2018,
  sum(native_hawaiian_int) filter (where year = 2019) as total_2019,
  sum(native_hawaiian_int) filter (where year = 2020) as total_2020,
  sum(native_hawaiian_int) filter (where year = 2021) as total_2021,
  (
    sum(native_hawaiian_int) filter (where year = 2021)) - (sum(native_hawaiian_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(native_hawaiian_int) filter (where year = 2021)) - (sum(native_hawaiian_int) filter (where year = 2020))) /
      sum(native_hawaiian_int) filter (where year = 2020) * 100,
  2) as percent_change
from districts
where state = :state
and grade = :grade
and term = :term
union
select
  'native_hawaiian_pacific_islander_int' as column,
  sum(native_hawaiian_pacific_islander_int) filter (where year = 2015) as total_2015,
  sum(native_hawaiian_pacific_islander_int) filter (where year = 2016) as total_2016,
  sum(native_hawaiian_pacific_islander_int) filter (where year = 2017) as total_2017,
  sum(native_hawaiian_pacific_islander_int) filter (where year = 2018) as total_2018,
  sum(native_hawaiian_pacific_islander_int) filter (where year = 2019) as total_2019,
  sum(native_hawaiian_pacific_islander_int) filter (where year = 2020) as total_2020,
  sum(native_hawaiian_pacific_islander_int) filter (where year = 2021) as total_2021,
  (
    sum(native_hawaiian_pacific_islander_int) filter (where year = 2021)) - (sum(native_hawaiian_pacific_islander_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(native_hawaiian_pacific_islander_int) filter (where year = 2021)) - (sum(native_hawaiian_pacific_islander_int) filter (where year = 2020))) /
      sum(native_hawaiian_pacific_islander_int) filter (where year = 2020) * 100,
  2) as percent_change
from districts
where state = :state
and grade = :grade
and term = :term
union
select
  'pacific_islander_int' as column,
  sum(pacific_islander_int) filter (where year = 2015) as total_2015,
  sum(pacific_islander_int) filter (where year = 2016) as total_2016,
  sum(pacific_islander_int) filter (where year = 2017) as total_2017,
  sum(pacific_islander_int) filter (where year = 2018) as total_2018,
  sum(pacific_islander_int) filter (where year = 2019) as total_2019,
  sum(pacific_islander_int) filter (where year = 2020) as total_2020,
  sum(pacific_islander_int) filter (where year = 2021) as total_2021,
  (
    sum(pacific_islander_int) filter (where year = 2021)) - (sum(pacific_islander_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(pacific_islander_int) filter (where year = 2021)) - (sum(pacific_islander_int) filter (where year = 2020))) /
      sum(pacific_islander_int) filter (where year = 2020) * 100,
  2) as percent_change
from districts
where state = :state
and grade = :grade
and term = :term
union
select
  'unknown_race_int' as column,
  sum(unknown_race_int) filter (where year = 2015) as total_2015,
  sum(unknown_race_int) filter (where year = 2016) as total_2016,
  sum(unknown_race_int) filter (where year = 2017) as total_2017,
  sum(unknown_race_int) filter (where year = 2018) as total_2018,
  sum(unknown_race_int) filter (where year = 2019) as total_2019,
  sum(unknown_race_int) filter (where year = 2020) as total_2020,
  sum(unknown_race_int) filter (where year = 2021) as total_2021,
  (
    sum(unknown_race_int) filter (where year = 2021)) - (sum(unknown_race_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(unknown_race_int) filter (where year = 2021)) - (sum(unknown_race_int) filter (where year = 2020))) /
      sum(unknown_race_int) filter (where year = 2020) * 100,
  2) as percent_change
from districts
where state = :state
and grade = :grade
and term = :term
union
select
  'white_int' as column,
  sum(white_int) filter (where year = 2015) as total_2015,
  sum(white_int) filter (where year = 2016) as total_2016,
  sum(white_int) filter (where year = 2017) as total_2017,
  sum(white_int) filter (where year = 2018) as total_2018,
  sum(white_int) filter (where year = 2019) as total_2019,
  sum(white_int) filter (where year = 2020) as total_2020,
  sum(white_int) filter (where year = 2021) as total_2021,
  (
    sum(white_int) filter (where year = 2021)) - (sum(white_int) filter (where year = 2020)
  ) as change_2020_2021,
  round(
    (1.0 * (
      sum(white_int) filter (where year = 2021)) - (sum(white_int) filter (where year = 2020))) /
      sum(white_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

Edit SQL