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?