Skip to content
Advertisement

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

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

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:

My code so far:

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:

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               
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement