Percent per race per group by districtname (another group)

Question

I need help in finding percent per row per column of race divided by the total count by district name. I am able to find the count using this code:

SELECT DISTINCT 
    SchoolYrCode
    ,DistrictID
    ,DistrictName
    ,SchoolName
        ,COUNT (case when RaceEthnicity = 'American Indian or Alaska Native'        
THEN 1 end) as 'Am Indian Alaska Native'
    ,COUNT (case when RaceEthnicity = 'Asian'                           THEN 1 end) as 'Asian'
    ,COUNT (case when RaceEthnicity = 'Black or African American'           THEN 1 end) as 'African American'
    ,COUNT (case when RaceEthnicity = 'Hispanic or Latino'              THEN 1 end) as 'Hispanic Latino'
    ,COUNT (case when RaceEthnicity = 'Native Hawaiian or Other Pacific Islander' THEN 1 end) as 'Hawaiian Pacific Is'
    ,COUNT (case when RaceEthnicity = 'White or Caucasian'              THEN 1 end) as 'Caucasian'
    ,COUNT (case when RaceEthnicity = 'Multiracial'                     THEN 1 end) as 'MultiRace'
    ,COUNT (case when RaceEthnicity = 'NotProvided/Unknown'             THEN 1 end) as 'Unknown'

    ,COUNT (case when Gender = 'Male'                               THEN 1 end) as MaleCount
    ,COUNT (case when Gender = 'Female'                             THEN 1 end) as FemaleCount
    ,COUNT (case when Gender = 'Non-Gender Binary'                      THEN 1 end) as XCount
    ,COUNT (case when Gender = 'NotProvided/Unknown'                    THEN 1 end) as UnknownGenderCount
FROM #temp
GROUP BY DistrictID, DistrictName, SchoolName, SchoolYrCode

And I get something like this:

SY           DistrictID   DistrictName   SchoolName   AM Indian  Asian   African Am  Hispinic...
2014-2015    00001       District1      School1        0 (%?)      0(%?)  0 (%?)     1 (%?)
2014-2015    00001       District1      School2        12  (%?)    10(%?)  3(%?)      0(%?)
2014-2015    00002       District2      School3        6  (%?)     2 (%?)  3(%?)    2(%?)

I am confident with the count results, but what I need to do next is to find the percent per entry under race, divided by the district total.

Example: a. for Am Indian School 2: count/district1 total i.e. 12/26 = 46.15% b. for Asian School3: 2/13 = 15.38%

I need help with the correct code/syntax?

Answer 1

You can use AVG():

AVG(case when RaceEthnicity = 'Asian' THEN 1.0 ELSE 0 END) as Asian,

Note the 1.0 and ELSE 0.

For a percentage between 0 and 100, you would just multiply by 100 or use:

AVG(case when RaceEthnicity = 'Asian' THEN 100.0 ELSE 0 END) as Asian,

Answer 2

You can either sum up all of the asian ethnicity columns or use a count of number of records and then decide this by each ethnicity group. I did a count of number of records since it's cleaner...

SELECT *, 'Am Indian Alaska Native'/'TotalRaceEthnicityByDistrictName'  * 100 as 'Am Percent', 'Asian'/'TotalRaceEthnicityByDistrictName' * 100 as 'Asian Percent', ..., 
'Unknown'/'TotalRaceEthnicityByDistrictName' * 100 as 'Unknown Percent'
FROM 
(   SELECT DISTINCT 
    SchoolYrCode
    ,DistrictID
    ,DistrictName
    ,SchoolName
    ,COUNT (case when RaceEthnicity = 'American Indian or Alaska Native'        THEN 1 end) as 'Am Indian Alaska Native'
    ,COUNT (case when RaceEthnicity = 'Asian'                           THEN 1 end) as 'Asian'
    ,COUNT (case when RaceEthnicity = 'Black or African American'           THEN 1 end) as 'African American'
    ,COUNT (case when RaceEthnicity = 'Hispanic or Latino'              THEN 1 end) as 'Hispanic Latino'
    ,COUNT (case when RaceEthnicity = 'Native Hawaiian or Other Pacific Islander' THEN 1 end) as 'Hawaiian Pacific Is'
    ,COUNT (case when RaceEthnicity = 'White or Caucasian'              THEN 1 end) as 'Caucasian'
    ,COUNT (case when RaceEthnicity = 'Multiracial'                     THEN 1 end) as 'MultiRace'
    ,COUNT (case when RaceEthnicity = 'NotProvided/Unknown'             THEN 1 end) as 'Unknown'
    ,COUNT (1) as 'TotalRaceEthnicityByDistrictName'

    ,COUNT (case when Gender = 'Male'                               THEN 1 end) as MaleCount
    ,COUNT (case when Gender = 'Female'                             THEN 1 end) as FemaleCount
    ,COUNT (case when Gender = 'Non-Gender Binary'                      THEN 1 end) as XCount
    ,COUNT (case when Gender = 'NotProvided/Unknown'                    THEN 1 end) as UnknownGenderCount
    FROM #temp
    GROUP BY DistrictID, DistrictName, SchoolName, SchoolYrCode
) z