sql - 你怎么能有两个 COUNT 查询 GROUPED BY SQL 中的同一列？

Question

我有两张表，一张名为 Employees，一张名为 Shop。 Employees 表包含所有员工的性别，而 Shop 表包含每个员工工作的商店的名称。

我需要计算男性和女性员工的数量，并按他们工作的商店对他们进行分组。例如，想要的结果如下所示:

FemaleEmployees MaleEmployees Store
1 2 埃德的硬件
3 4 杂货店

到目前为止我有这个:

SELECT COUNT(Gender) AS FemaleEmployees, StoreName AS Store
FROM Employees 
JOIN Store
ON Employees.EmployeeID = Store.EmployeeID
Where Gender = 'F'
GROUP BY StoreName;

SELECT COUNT(Gender) AS MaleEmployees, StoreName AS Store
FROM Employees
JOIN Store
ON Employees.EmployeeID = Store.EmployeeID
Where Gender = 'M'
GROUP BY StoreName;

这显然在两个选择查询中给出了所需的答案，尽管我需要它在一个查询中。我只需要帮助将两者转化为单一查询格式。

我尝试过使用两个子查询，但到目前为止没有任何效果。我知道如何得到答案，只是不是以所需的格式。如果有人能帮我解决这个问题，我将不胜感激。

Answer 1

你可以这样做

SELECT SUM(CASE WHEN Gender='F' THEN 1 ELSE 0 END) As FemaleEmployees,
       SUM(CASE WHEN Gender='M' THEN 1 ELSE 0 END) As MaleEmployees,
       storeName
FROM Employees 
JOIN Store
ON Employees.EmployeeID = Store.EmployeeID
GROUP BY storeName