If you have a category table similar like the one below, and have the need to count the children elements, you can do it following the example below.
| ID | CATEGORY_NAME | PARENT_ID |
|---|---|---|
| 1 | Cars | 0 |
| 2 | Opel | 1 |
| 3 | Planes | 0 |
| 4 | Mercedes | 1 |
| 5 | Cessna | 3 |
| 6 | Airbus | 3 |
| 7 | Boats | 0 |
SELECT
T1.ID,
T1.CATEGORY_NAME,
COUNT(T2.ID) AS CHILDREN
FROM
TABLE AS T1
LEFT JOIN TABLE AS T2 ON T1.ID = T2.PARENT_ID
GROUP BY
T1.ID
As you can see, we did a self join of the same table and created a relationship with the table alias T1.ID with the table alias T2.PARENT_ID.
And, if you want, for example to get the categories with no children, just do it like this, by adding the two final lines:
SELECT
T1.ID,
T1.CATEGORY_NAME,
COUNT(T2.ID) AS CHILDREN
FROM
TABLE AS T1
LEFT JOIN TABLE AS T2 ON T1.ID = T2.PARENT_ID
GROUP BY
T1.ID
HAVING
CHILDREN = 0
