Find how many times each value from column A appears in column B in the same table

I’m new to SQL, I’m learning from a free youtube MySQL course for beginners.

2 days ago I gave myself a problem and now I found a work-around, but perhaps there are better ways to solve it.

emp_id marks each employee, super_id – their supervisor. I wanted to find how many subordinates each employee has.

This is just a problem for my learning purposes, it has no significance in the real world. If this is really easy and I’m overcomplicating, I’m sorry. I’m using a self-join, because it’s just one table here.

I found this on google:

SELECT A1.A, COUNT(*)
  FROM Anonymous AS A1
  JOIN Anonymous AS A2 ON A1.A = A2.B
 GROUP BY A1.A;

After tweaking it like this it almost worked, except that it returns 1 subordinate for each employee that should have 0 subordinates:

SELECT emp1.emp_id, COUNT(*) AS Subordinates
FROM employee AS emp1
LEFT JOIN employee AS emp2
ON emp1.emp_id = emp2.super_id
GROUP BY emp1.emp_id
;

I’ve no idea why and how self-join works by the way… But it works for my task. After the following tweak it does everything that I wanted, BUT if there is an employee with exactly one subordinate, it will say he has none (I suppose). So it’s not a real solution. And there might be a simpler or more elegant way to do it?

SELECT emp1.emp_id, IF (COUNT(*)>1, COUNT(*), NULL) AS Subordinates
FROM employee AS emp1
LEFT JOIN employee AS emp2
ON emp1.emp_id = emp2.super_id
GROUP BY emp1.emp_id
;

Result

>Solution :

What about using COUNT(DISTINCT ...) on the self joined table?

select e1.emp_id,
       count(distinct e2.emp_id) as subordinates
  from employee as e1
  left join employee as e2
  on e1.emp_id = e2.super_id
group by e1.emp_id

Fiddle: https://www.db-fiddle.com/f/322VZ9CfYjMMtg8i81T2Dd/0