microsoftsql-返回各部门男女员工总数

wz8daaqr 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(259)

我正在努力解决这个问题。
基本上，我需要做的是创建一个查询，返回一个统计每个部门的所有女性、男性和总员工的表。
到目前为止，我已经能够显示所有部门以及女性、男性和员工的总数，但不能显示每个部门（它显示每个部门的相同数量）。
如何只显示每个部门的男性、女性和员工总数？？
问题和代码如下。以及代码生成的输出。
提前谢谢各位！

-- 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

输出结果如下

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

sql mysql ddl dml

来源：https://stackoverflow.com/questions/62226356/microsoft-sql-return-amount-of-male-female-and-total-staff-for-each-respectiv

1条答案

按热度按时间

kzipqqlq1#

使用条件聚合：

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

赞(0）回复(0）举报 2021-07-26

我来回答

microsoftsql-返回各部门男女员工总数

1条答案

相关问题

热门标签

最新问答