List all employees and the number of people they supervise in mysql

Question

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'

Sample data and expected results, please. PS - 'some' have supervisor field? — user3741598, Feb 01 '17 at 21:35
"all the others having no one to supervise as having 1 instead of 0" - This is because of `COUNT(*)`. Try `COUNT(e.supervisor_id)` — Paul Spiegel, Feb 01 '17 at 21:53

score 1 · Answer 1 · answered Feb 01 '17 at 21:50

We join your employee table with a select like we would join it with a real table. The select will consist of all supervisor_ids and the number of occurences in the supervisor_id field (records where supervisor_id is null will be ignored).

SELECT e.employee_id, e.employee_name, s.supervising 
FROM employee e
LEFT JOIN (SELECT supervisor_id, count(*) as supervising 
           FROM employee
           WHERE supervisor_id is NOT NULL
           GROUP BY supervisor_id) AS s
ON(e.employee_id = s.supervisor_id)

Thanks, that works as expected. Paul's suggestion also worked within what I already had. Get to have more that one way of thinking about a problem. — user2328273, Feb 01 '17 at 22:01

score 0 · Accepted Answer · answered Feb 01 '17 at 22:02

0

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;

answered Feb 01 '17 at 22:02

Paul Spiegel

30,925
5
44
53

Could you explain a little why a `left` or `right` with `count(*)` is always at least one? Just so I have a better understanding of how it works. – user2328273 Feb 01 '17 at 22:10
@user2328273 `COUNT(*)` will count all rows that have at least one column with a NOT NULL value. While `COUNT(columnName)` only counts rows with NOT NULL values in that column. Look at the example [here](http://rextester.com/JYPH60307) - There is no row containing only NULLs. – Paul Spiegel Feb 01 '17 at 22:20
@user2328273 Also have a look at the first comment [here](https://dev.mysql.com/doc/refman/5.7/en/counting-rows.html) – Paul Spiegel Feb 01 '17 at 22:29

List all employees and the number of people they supervise in mysql

2 Answers2