按多列分組，然後計算同一列的不同值

我想獲得一個結果，它告訴我每個地區每種殘疾類型的男性和女性人數。每個區都可能有多種殘疾。

到目前為止，我已經達到以下查詢：

 SELECT 
 DistrictId, 
 fb.DisabilityTypeId, 
 SUM(
   CASE WHEN GenderId = 1 THEN 1 ELSE 0 END
 ) AS Male, 
 SUM(
   CASE WHEN GenderId = 2 THEN 1 ELSE 0 END
 ) AS Female 
FROM 
 Districts d 
 LEFT OUTER JOIN FormAddresses a ON d.Id = a.DistrictId 
 INNER JOIN PeopleForms pf ON a.PeopleFormId = pf.Id 
 INNER JOIN FormBeneficiaries fb ON pf.Id = fb.PeopleFormId 
 INNER JOIN FormPersonalInfos fp ON pf.Id = fp.PeopleFormId 
where 
 a.IsDeleted = 0 
 AND pf.FormTypeId = 2 
 AND d.CityId = 3 
GROUP BY 
 DistrictId, 
 fp.GenderId,
 fb.DisabilityTypeId

這給出了以下結果：

DistrictId  |   DisabilityTypeId    |   Male  | Female
   1       |       2               |   1     |     0
   3       |       2               |   0     |     3
   5       |       16              |   1     |     0
   5       |       20              |   2     |     0
   5       |       20              |   0     |     1

但我想達到以下結果：

DistrictId  |   DisabilityTypeId    |   Male  | Female
   1       |       2               |   1     |     3
   5       |       16              |   1     |     0
   5       |       20              |   2     |     1

我以某種方式設法獲得了預期的結果，但是在每個性別的 select 子句中都有一些複雜的子查詢，我不喜歡並且不確定性能。

我怎樣才能為我想要的結果編寫一個有效的查詢？

謝謝。

從GROUP BY子句中刪除性別：

SELECT DistrictId
    , fb.DisabilityTypeId
    , SUM( CASE WHEN GenderId = 1 THEN 1 ELSE 0 END ) AS Male
    , SUM( CASE WHEN GenderId = 2 THEN 1 ELSE 0 END ) AS Female 
FROM Districts d 
LEFT JOIN FormAddresses a 
   ON d.Id = a.DistrictId 
JOIN PeopleForms pf 
   ON a.PeopleFormId = pf.Id 
JOIN FormBeneficiaries FB 
   ON pf.Id = fb.PeopleFormId 
JOIN FormPersonalInfos fp 
   ON pf.Id = fp.PeopleFormId 
WHERE a.IsDeleted = 0 
 AND pf.FormTypeId = 2 
 AND d.CityId = 3 
GROUP BY DistrictId
      , fb.DisabilityTypeId

引用自：https://dba.stackexchange.com/questions/282097