How to display a list of managers with the number of their subordinates from a table?

Question

SQL

Tell me, please, how can I display a list of managers with the number of their subordinates from a table like this:


I got such a query, but the problem is that it does not display those managers who have no subordinates.

SELECT
  m.name,
  m.position,
  COUNT(e.name) AS number_of_subordinates
FROM
  employees as e
  JOIN employees as m
  ON e.manager_id = m.id
GROUP BY m.name;

And a small related question - I'm trying to display a list of the type "Employee - Head", all records are displayed except for the record of employees for which no managers are indicated.

SELECT e.name as Employee, e.position as Position, m.name as Manager
FROM employees e,
     employees m
WHERE e.manager_id = m.id;

Answer 1

Try

SELECT
    m.name,
    m.position,
    COUNT(e.name) AS number_of_subordinates
FROM
    employees as e
    RIGHT JOIN employees as m
    ON e.manager_id = m.id
GROUP BY m.name
WHERE m.is_manager = 1

SELECT 
    e.name as Employee, 
    e.position as Position, 
    m.name as Manager
FROM
    employees as e
    LEFT JOIN employees as m
    ON e.manager_id = m.id