按地区名称(另一组)分组的每个种族的百分比

Question

我需要帮助查找种族的每行每列百分比除以按地区名称的总计数。我可以使用以下代码找到计数：

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

我得到了这样的东西：

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(%?)

我对计票结果很有信心，但我接下来需要做的是找到每个参赛项目的百分比，除以地区总数。

示例：a.对于Am Indian学校2：计数/分区1总数，即12/26=46.15%b.亚洲学校3：2/13=15.38%

我需要正确的代码/语法帮助吗？

Answer 1

您可以汇总所有亚洲种族列，也可以使用记录数，然后根据每个种族组来决定。我数了几张唱片，因为它更干净。。。

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

Answer 2

您可以使用AVG():

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

注意1.0和ELSE 0。

对于0到100之间的百分比，您只需乘以100或使用：

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