sql: count for at least one

Question

Given two tables Employee and Task, I am trying to find the number of employees who have at least one task assigned for each type of employee.

[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

I have tried the following:

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

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: