查询出现字段内的值的次数出现最多并排列，怎样写这个SQL？

[myname1987111]

user表内字段：parent_id 保存的是推荐人的ID,
我想统计parent_id 里出现的次数，并按高到低排列出来，同时显示推荐人的名字
请问这个SQL怎么写？

[vivian_lanlan]

SELECT
    A.parent_id,B.user_name,COUNT(*) AS rowcnt
FROM [user] AS A
    JOIN [user] AS B
ON A.parent_id = B.user_id
GROUP BY A.parent_id,B.user_name
ORDER BY COUNT(*) DESC;

[Zoezs]

select parent_id,count(1) as qty from user
    group by parent_id
    order by count(1) desc

[Zoezs]

select parent_id,count(1) as qty from user
    group by parent_id
    order by count(1) desc