SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE video(
   videoId   VARCHAR(8) NOT NULL PRIMARY KEY
  ,channelId INTEGER  NOT NULL
);

INSERT INTO video(videoId,channelId) VALUES ('a',1);
INSERT INTO video(videoId,channelId) VALUES ('b',1);
INSERT INTO video(videoId,channelId) VALUES ('c',2);
INSERT INTO video(videoId,channelId) VALUES ('d',2);
CREATE TABLE comments(
   commentID         VARCHAR(10) 
  ,replyID           VARCHAR(9)
  ,videoID           VARCHAR(9) 
  ,videoID_channelID INTEGER  
  ,text              VARCHAR(9) 
);

INSERT INTO comments(commentID,replyID,videoID,videoID_channelID,text) VALUES ('c1',NULL,'a',1,'test');
INSERT INTO comments(commentID,replyID,videoID,videoID_channelID,text) VALUES ('c2',NULL,'a',1,'some text');
INSERT INTO comments(commentID,replyID,videoID,videoID_channelID,text) VALUES (NULL,'r1.c1','b',1,'asdad');
INSERT INTO comments(commentID,replyID,videoID,videoID_channelID,text) VALUES (NULL,'r2.c1','b',1,'xxx');
INSERT INTO comments(commentID,replyID,videoID,videoID_channelID,text) VALUES ('c5',NULL,'b',1,'yyy');
INSERT INTO comments(commentID,replyID,videoID,videoID_channelID,text) VALUES ('c6',NULL,'c',2,'zzz');
INSERT INTO comments(commentID,replyID,videoID,videoID_channelID,text) VALUES ('c7',NULL,'d',2,'-...-.');
select t.*, sum(commentCount) over(partition by channelId) commentCount_sum
from (
    select v.videoId, v.channelId, count(*) commentCount
    from video v
    inner join comments c on c.videoID = v.videoId and c.videoID_channelID = v.channelId
    group by v.videoId, v.channelId
) t


videoId
channelId
commentCount
commentCount_sum


a
1
2
5

b
1
3
5

c
2
1
2

d
2
1
2