Group by Multiple columns and then count different value of same column I want to achieve the result which tells me the number of males and females of each disability types in each district.
each district can have multiple disabilities.
So far i have reached  the following query :
  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

which gives the following result  :
DistrictId  |   DisabilityTypeId    |   Male  | Female
    1       |       2               |   1     |     0
    3       |       2               |   0     |     3
    5       |       16              |   1     |     0
    5       |       20              |   2     |     0
    5       |       20              |   0     |     1





But i want to achieve the following result :
DistrictId  |   DisabilityTypeId    |   Male  | Female
    1       |       2               |   1     |     3
    5       |       16              |   1     |     0
    5       |       20              |   2     |     1

i somehow managed to get the expected result, but that with some complex sub-queries in the select clause  of each gender which i didnt like and was not sure about the performance.
how can i write an efficient query for the desired result that i want?
Thanks.
Remove gender from GROUP BY clause:
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