Postgres: column “all_comments.id” must appear in the GROUP BY clause or be used in an aggregate function

I am using with recursive to count the number of all descendants. That would be the following SQL query, but if I specify only root_id for group by, I get I get the following error.

column "all_comments.id" must appear in the GROUP BY clause or be used in an aggregate function

I don’t understand why this error occurs with all_comments.id. Also, if I add all_comments.id to group by, this error does not occur, but The original purpose of the all_descendants.id does not count well, and rows of all descendants are returned.

I do not want to remove this column from the SELECT because we need to retrieve the id and other columns as well.

What can I do to resolve this? Your help would be greatly appreciated.

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

Sample data and desired output

                Table "public.videos_productvideocomment"
    Column    |           Type           | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
 id           | uuid                     |           | not null |
 created_at   | timestamp with time zone |           | not null |
 updated_at   | timestamp with time zone |           | not null |
 text         | text                     |           | not null |
 commented_at | timestamp with time zone |           | not null |
 edited_at    | timestamp with time zone |           | not null |
 parent_id    | uuid                     |           |          |
 user_id      | uuid                     |           | not null |
 video_id     | uuid                     |           | not null |

[
  {
    id: "uuid1",
    text: "...",
    reply_count: 10,
  },
  {
    id: "uuid5",
    text: "...",
    reply_count: 3,
  },
]

— Edit — Using the window function

with recursive all_comments as (
   select id, parent_id, id as root_id
   from videos_productvideocomment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from videos_productvideocomment c
     join all_comments p on c.parent_id = p.id
)
select id, parent_id, root_id, count(root_id) OVER(PARTITION BY root_id) -1 as all_descendants_count
from all_comments

Results obtained

[
  {
    id: "uuid1",
    parent_id: null,
    text: "...",
    reply_count: 10,
  },
  {
    id: "uuid5",
    parent_id: null,
    text: "...",
    reply_count: 3,
  },
  {
    id: "uuid8",
    parent_id: "uuid1",
    text: "...",
    reply_count: 0,
  },
  ...
]

We need to get only those with parent_id null, but if we add WHERE parent_id is null, all_descendants_count will be 0.

Answer

Add a filter step after counting over the entire dataset, for example

with recursive all_comments as (
   select id, parent_id, id as root_id
   from videos_productvideocomment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from videos_productvideocomment c
     join all_comments p on c.parent_id = p.id
)
select *
from (
    select id, parent_id, root_id, count(root_id) OVER(PARTITION BY root_id) -1 as all_descendants_count
    from all_comments
) t
where id = root_id