How to get the number of child categories by an additional field?

Question

MySQL

Hello.
I can't figure out how to make a query to the database...
There is a table with columns id, parent_id, name
This is a table of categories, subcategories, subsubs, etc.
I want to get the top-level categories (parent_id = 0) and an additional field, for example, sub_count, which will contain the number of subcategories belonging to the parent.
Can this be done with one request?

Answer 1

select t.id, t.parent_id, t.name,
  (select count(*) from table tmp where t.id = tmp.parent_id) child_count
from table t
where t.parent_id is null or t.parent_id = 0