I have a table of employees. Each employee has an employee id. Some employees have a supervisor field that links back to another employee's id. There are 10 employees, two of which are supervisors, each supervising 4 people. I am trying to get a list of all the employees and the number of other employees they supervise. So far I can only seem to get the supervisors and the number they supervise to show. This is my query:
SELECT s.employee_name, COUNT(*)
FROM employee e
join employee s on e.supervisor_id= s.employee_id
group by s.
order by s.employee_name;
I tried changing JOIN to RIGHT JOIN and it will now show me all 10 employees with the two supervisors shown as having 4 people they supervise but it shows all the others having no one to supervise as having 1 instead of 0. I'm sure it's something simple I am missing.
Sample Data:
employee_name, employee_name, supervisor_id,
'10111', 'Sydnee K. Stevens' NULL
'10870', 'Colton C. Rocha', '10111'
'11425', 'Astra V. Sharp','10111'
'12973', 'Melanie X. Rojas','10111'
'14451', 'Bethany Roman','10111'
'14597', 'Lydia Edwards', NULL
'16153', 'Selma Q. Conley', '14597'
'17730', 'Kristen B. Malone', '14597'
'17762', 'Barrett B. Bauer', '14597'
'18628', 'Shana Z. Flowers','14597'
Using LEFT or RIGHT JOIN COUNT(*) will always be at least 1. If using LEFT JOIN (RIGHT JOIN is confusing) you just need to count values from a right table column. COUNT(column) will ignore all rows with NULL in that column.
SELECT s.*, COUNT(e.supervisor_id) as num_supervised
FROM employee s
LEFT JOIN employee e on e.supervisor_id = s.employee_id
group by s.employee_id
order by s.employee_name;