Retrieve additional columns in recursive CTE This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep.

I manage to do it with two separate queries but there has to be a way to just use one and get the count of the children:

with recursive all_comments as (
   select id, parent_id, id as root_id
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from comment c
     join all_comments p on c.parent_id = p.id
)
select root_id, count(*) as comment_count
from all_comments
group by root_id;


How would I pull content column from the parent comment in this fiddle?

http://sqlfiddle.com/#!15/158ea/15
Your fiddle does not open, so I'm not sure if I understand correctly what you have and what you want, but I'd venture to guess you need to join back to comment on id, something like this:

with recursive all_comments as (
   select id, parent_id, id as root_id
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from comment c
     join all_comments p on c.parent_id = p.id
)
select a.root_id, a.comment_count, com.content from 
(select root_id, count(*) as comment_count
from all_comments
group by root_id) as a
inner join
comment as com
 on a.root_id = com.id and com.parent_id is null;