mysql> select * from ref1;
+----+------+-----+
| id | name | fid |
+----+------+-----+
| 1 | aaa | 0 |
| 2 | bbb | 1 |
| 3 | ccc | 2 |
| 4 | ddd | 1 |
+----+------+-----+
4 rows in set

mysql> SELECT
r1.id AS user_id,
r1. NAME AS user_name,
count(DISTINCT r2. NAME) AS ref_count
FROM
ref1 r1
INNER JOIN ref1 r2 ON r1.id = r2.fid
GROUP BY
r1. NAME;

+---------+-----------+-----------+
| user_id | user_name | ref_count |
+---------+-----------+-----------+
| 1 | aaa | 2 |
| 2 | bbb | 1 |
+---------+-----------+-----------+
2 rows in set