Is it possible to simplify SQL Query

Question

I take need to take count depend on different values. I am using this one. Can I simplify that.

SELECT 
[FC].[Name] AS [JobCategory],
(SELECT COUNT(*) 
  FROM [dbo].[JobCandidateHire] AS [JCH]
  LEFT JOIN [dbo].[Job] AS [J] ON [J].[ID]=[JCH].[JobID]
  LEFT JOIN [dbo].[Candidate] AS [C] ON [C].[ID]=[JCH].[CandidateID]
  LEFT JOIN [dbo].[JobCandidateHireLevel] AS [JCHL] ON [JCHL].[ID]=[JCH].[JobCandidateHireLevelID]
  WHERE [JCHL].[LevelType]=5 AND [J].[FunctionalCategoryID] =[FC].[ID]  AND [C].[EthnicityID]=5 AND [C].[GenderID]=2
) AS [HMale],
(SELECT COUNT(*) 
  FROM [dbo].[JobCandidateHire] AS [JCH]
  LEFT JOIN [dbo].[Job] AS [J] ON [J].[ID]=[JCH].[JobID]
  LEFT JOIN [dbo].[Candidate] AS [C] ON [C].[ID]=[JCH].[CandidateID]
  LEFT JOIN [dbo].[JobCandidateHireLevel] AS [JCHL] ON [JCHL].[ID]=[JCH].[JobCandidateHireLevelID]
  WHERE [JCHL].[LevelType]=5 AND [J].[FunctionalCategoryID] =[FC].[ID] AND [C].[EthnicityID]=2 AND [C].[GenderID]=3
) AS [AmericanIndianFemale],
(SELECT COUNT(*) 
  FROM [dbo].[JobCandidateHire] AS [JCH]
  LEFT JOIN [dbo].[Job] AS [J] ON [J].[ID]=[JCH].[JobID]
  LEFT JOIN [dbo].[Candidate] AS [C] ON [C].[ID]=[JCH].[CandidateID]
  LEFT JOIN [dbo].[JobCandidateHireLevel] AS [JCHL] ON [JCHL].[ID]=[JCH].[JobCandidateHireLevelID]
  WHERE [JCHL].[LevelType]=5 AND [J].[FunctionalCategoryID] =[FC].[ID] AND [C].[EthnicityID]=7 AND [C].[GenderID]=3
) AS [TwoMoreRaceFemale],
(SELECT COUNT(*) 
  FROM [dbo].[JobCandidateHire] AS [JCH]
  LEFT JOIN [dbo].[Job] AS [J] ON [J].[ID]=[JCH].[JobID]
  LEFT JOIN [dbo].[Candidate] AS [C] ON [C].[ID]=[JCH].[CandidateID]
  LEFT JOIN [dbo].[JobCandidateHireLevel] AS [JCHL] ON [JCHL].[ID]=[JCH].[JobCandidateHireLevelID]
  WHERE [JCHL].[LevelType]=5 AND [J].[FunctionalCategoryID] = [FC].[ID]
) AS [CategoryTotal],
[FC].[IsArchived]    
FROM [dbo].[FunctionalCategory] AS [FC]

Here the join is same for sub query, just where condition is different.

Answer 1

The query below works syntactically, but without any schema or sample data provided it is the best estimate of a simpler query.

select fc.name as JobCateogry
, sum(case when jchl.LevelType = 5 and c.EthnicityID = 5 and c.GenderID = 2 then 1 else 0 end) as HMale
, sum(case when jchl.LevelType = 5 and c.EthnicityID = 2 and c.GenderID = 3 then 1 else 0 end) as AmericanIndianFemale
, sum(case when jchl.LevelType = 5 and c.EthnicityID = 7 and c.GenderID = 3 then 1 else 0 end) as TwoMoreRaceFemale
, count(*) as CategoryTotal
, fc.IsArchived
from dbo.FunctionalCategory as fc
left join dbo.Job as j on fc.ID = j.FunctionalCategoryID
left join dbo.JobCandidateHire as jch on j.ID = jch.JobID
left join dbo.Candidate as c on c.id = jch.CandidateID
left join dbo.JobCandidateHireLevel as jchl on jchl.ID = jch.JobCandidateHireLevelID
group by fc.name
, fc.IsArchived