How to display number of employees of each position in each department

I have two tables, named Employee and Department.

Employee (Emp_ID, Name, Position, DeptID)

Department (Department_ID, Dept_Name).

The position of an employee may be tech_support, data_entry, or assistant manager.

I want to display the name of each department along with the number of employees working in each position such that each position displayed in a separate column.

If the dept has no employees working in a position, the number displayed shall be zero.

Sample output:

Dept_Name         tech_support       data_entry        assistant manager
Accounts               5                 6                 2
Production             2                 4                 2
Sales/Marketing        0                 6                 3

Answer

With a LEFT JOIN of department to employee and conditional aggregation:

select
  d.dept_name,
  sum(case when e.position = 'tech_support' then 1 else 0 end) tech_support,
  sum(case when e.position = 'data_entry' then 1 else 0 end) data_entry,
  sum(case when e.position = 'assistant_manager' then 1 else 0 end) assistant_manager
from department d left join employee e
on e.deptid = d.department_id
group by d.department_id, d.dept_name