0

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.

8
  • 1
    Do you want the smallest DistrictId for each DisabilityTypeId? Also, provide sample data in terms of INSERT statements, much more useful than asci-art tables.
    – Lennart
    Dec 24 '20 at 8:07
  • @Lennart no its not related to the smallest or largest Ids. the data is a bit complex to provide. we have disabled people in each district, and i want to get the number of males and females for each disability type. is it clear? Dec 24 '20 at 8:16
  • for example in districtId 1 i have X males and Y females that have blindness. Dec 24 '20 at 8:19
  • I dont understand why the blind females in district 3 ends up in district 1. Your query looks correct as far as I can tell, is the some additional information that needs to be taken into concideration?
    – Lennart
    Dec 24 '20 at 8:48
  • @Lennart its not like that the same blind female in district 3 also end up in district 1, they are different, we can have different numbers of males and females that are blind in each district/city. Dec 24 '20 at 9:01
3

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
2
  • Thank you very much, i didnt know that we can access columns in the select clause that are not grouped by Dec 24 '20 at 9:17
  • In general that is true, but GenderId is part of an aggregate function ( SUM ), so that is perfectly legal.
    – Lennart
    Dec 24 '20 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.