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?

## Advertisement

## 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`

).

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

**9**People found this is helpful