mysql self-refrencing table returning id of parent and count of children

I have a self referencing table and I want a mysql query that will return only the top most parent (parent = 0) and the number of children belonging to each of those. This is what I have so far, but I know it will not work.

SELECT id, (SELECT COUNT(id) FROM example where parent_id = id) FROM example WHERE parent_id = 0;

+--------+-----------+
|   id   | parent_id |
+--------+-----------+
|    1   |     0     |
|    2   |     1     |
|    3   |     1     |
|    4   |   开发者_StackOverflow  0     |
|    5   |     4     |
+--------+-----------+

SELECT parent_id parent, count( * ) num_children
FROM example
GROUP BY parent_id
HAVING parent_id
IN (

SELECT id
FROM `example`
WHERE parent_id =0
)

Something as simple as this should work:

SELECT parent_id, count( * ) cnt
FROM example
WHERE parent_id
IN (
   SELECT id
   FROM `example`
   WHERE parent_id =0
)
GROUP BY parent_id

Thanks to the answers provided by Dave Morris and Tomgrohl I was able to get it to work. Here is the MySQL I used.

SELECT parent_id parent, count( * ) num_children
FROM example
GROUP BY parent_id
HAVING parent_id <> 0
IN (
    SELECT id
    FROM `example`
    WHERE parent_id = 0
);

You'll have to prefix columns with table alias

SELECT id, (SELECT COUNT(inner.id) FROM example inner where inner.parent_id = outer.id) FROM example outer WHERE parent_id = 0;