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