Get comments per video and total sum of comments per video channel

In addition to Get total sum of video comments for each video I noticed that I can’t use v.commentCount because I cleaned some data and now the column is inaccurate.

So I need to count the videoId comments and the total videoID comments with the comment table.

I have a table video

videoId | channelId
a       | 1        
b       | 1        
c       | 2
d       | 2        

and a table comments. video.channelId maps to comments.videoID_channelID

   commentID | replyID | videoID | videoID_channelID | textOriginal
   c1        | NULL    | a       | 1                 | "test"
   c2        | NULL    | a       | 1                 | "some text"
   NULL      | r1.c1   | b       | 1                 | "asdad"
   NULL      | r2.c1   | b       | 1                 | "xxx"
   c5        | NULL    | b       | 1                 | "yyy"
   c6        | NULL    | c       | 2                 | "zzz"
   c7        | NULL    | d       | 2                 | "-...-."

For every videoId I need the commentCount (how many comments are there per videoId) and the sum of all commentCounts for that channel (how many total comments are there for every video of a channelID?).

So the end result should be:

videoId | channelId | commentCount | commentCount_sum
a       | 1         | 2            | 5
b       | 1         | 3            | 5
c       | 2         | 1            | 2
d       | 2         | 1            | 2

My code so far:

SELECT v.videoId,
        v.channelId,,,
        count(c.textOriginal) commentCount,
        count(c.textOriginal) over (partition by c.videoID_channelID) as commentCount_sum,
FROM videos v
JOIN comments c
ON v.videoId = c.videoID
GROUP BY v.videoId
ORDER BY commentCount_sum ASC

but I don’t get the correct commentCount_sum?

Answer

You can aggregate in a subquery, and then do a window count in the outer query:

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

I also fixed your join conditions (you were missing a join condition on channelId).

Demo on DB Fiddle:

videoId | channelId | commentCount | commentCount_sum
:------ | :-------- | :----------- | :---------------
a       | 1         | 2            | 5               
b       | 1         | 3            | 5               
c       | 2         | 1            | 2               
d       | 2         | 1            | 2