COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

I'd like to do the following in one query using MySQL:

• grab a row that has a parent_id of 0

• grab a count of all the rows that have a parent_id of the row that we grabbed which has a parent_id of 0

How can I accomplish this in one query? Please let me know if you need more information, I will gladly be as assistive as I can. I'm not an expert on creating questions so please tell me what more information you need.

Here's an example of what I'm doing now:

select id from messages where parent_id=0

and then

select count(id) from messages where parent_id={{previously_chosen_id}}

How do I get a one shot query? Something like...

select id, count(records where parent_id=the id we just asked for)

Or, is there a better way to handle this? You see, currently I have to run a ton of queries to find the counts, when I'd rather do it in one shot.

This is easily achievable with an in-line subquery :

select  m.id,
        (select count(*) from messages where parent_id= m.id ) as ChildCount
from messages m
where m.parent_id = 0

Note that no group by is needed because a sub-query is used.

StackExchange Database Administrators Q#46332, answer score: 8