sql - sql：至少计算一个

Question

给定两个表 Employee 和 Task，我试图找出为每种类型的员工分配了至少一项任务的员工数量。

[Employee]
Id  employeeType
--------------------
111 developer
222 developer
333 designer
444 designer
555 owner

[Task]
Type    assignedEmployeeId
--------------------

A   111
B   222
C   333
D   111

<Desired Output>
employeeType     numEmp
--------------------
developer   2          <- developer with id 111 has two task assigned and 222 has 1 task
designer    1
owner       0

我尝试了以下方法：

SELECT Employee.id
FROM Employee LEFT OUTER JOIN Task
ON Employee.id = Task.assignedEmployeeid
GROUP BY Employee.id
HAVING COUNT (Employee.id) >= 1

标签： sql

Answer 1

Since you want to calculate type wise employee count you need to group by emloyeetype. And you want to count emloyeeid only if It's in task table. so count will be on task.assignedemployeeid.

select employeetype,count(distinct task.assignedemployeeid) numemp
from employee left join task on assignedemployeeid=id
group by employeetype

Output: