mysql - INNER JOIN COUNT on same table -

here's trying achieve. have users table want join itself. query users no parentuserid , want additional field called childrencount can find count of users parentuserid = userid.

select p.*, count(*) childrencount users p left join users q on q.userid = p.parentuserid p.parentuserid = 0 , p.categoryid = 2 order p.userid desc limit 10 offset 0 

i not getting error query isn't working expected. not count of related children. database mysql.

if understood correctly, can use correlated query :

select p.*,         (select count(*) users s         s.parentuserid = p.userid) childrencount  users p p.parentuserid = 0 , p.categoryid = 2  order p.userid desc limit 10 offset 0