Microsoft SQL - Return amount of male, female and total staff for each respective department

Question

I am struggling with this problem.

Essentially, what I need to do is create a query that returns a table that COUNTs all the female, male and total staff for each respective department.

So far, I've been able to display all the departments and the total number of female, male and staff but not for each individual department (it displays the same amount for every department).

How do I only display the male, female and total staff for each department??

The problem and the code are below. As well as the output that the code produces.

Thanks in advance guys!

-- Username:
-- Q1004 For each of the different departments, show the number of current female, male and total
-- employees currently employed.
-- Schemas: HumanResources

-- Example output:
-- departmentName   female  male    totalEmployeeCount
-- Production        40     120         160

-- Write your query below


SELECT Name AS departmentName, 
(SELECT COUNT(gender) FROM AdventureWorksDB.HumanResources.Employee WHERE Gender = 'F') AS female,
(SELECT COUNT(gender) FROM AdventureWorksDB.HumanResources.Employee WHERE Gender = 'M') AS male,
(SELECT COUNT(gender) FROM AdventureWorksDB.HumanResources.Employee) AS totalEmployeeCount

FROM AdventureWorksDB.HumanResources.Department N 
JOIN AdventureWorksDB.HumanResources.EmployeeDepartmentHistory EDH 
ON N.DepartmentID = EDH.DepartmentID
JOIN AdventureWorksDB.HumanResources.Employee E
ON EDH.BusinessEntityID = E.BusinessEntityID
GROUP BY Name

And the Output looks like this

Department                female|male   |totalEmployeeCount
Engineering                84   |206    |290
Tool Design                84   |206    |290
Sales                      84   |206    |290
Marketing                  84   |206    |290
Purchasing                 84   |206    |290
Research and Development   84   |206    |290
Production                 84   |206    |290

Answer 1

Use conditional aggregation:

SELECT Name AS departmentName, 
       SUM(CASE WHEN e.gender = 'F' THEN 1 ELSE 0 END) as famale,
       SUM(CASE WHEN e.gender = 'M' THEN 1 ELSE 0 END) as male,
       COUNT(*) as totalEmployeeCount
FROM AdventureWorksDB.HumanResources.Department N JOIN 
     AdventureWorksDB.HumanResources.EmployeeDepartmentHistory EDH 
     ON N.DepartmentID = EDH.DepartmentID JOIN
     AdventureWorksDB.HumanResources.Employee E
     ON EDH.BusinessEntityID = E.BusinessEntityID
GROUP BY Name