CODEWITHSUNDEEP
mysqlsql
FrenchMajesty
FrenchMajesty

Reputation: 1139

SQL Count rows in another table and join

I have 2 table in my database. The first is comments and the other is comments_votes. I want to select all comments, and for each comment, select all it's votes from comments_votes, add them up together and join it with the first query as totalVote.

My comments table look like:

id      comment      video_id   date_sent
----------------------------------------
5      "...."           99        "2017-05-23"
18      "...."          99        "2017-05-23"

comments_votes table look like:

id      user_id      comment_id   vote
----------------------------------------
45         86           5         1
45         23           5         1
78         12            18        -1

And the final wished result would look like:

id      comment   video_id   votes_total
----------------------------------------
5      " ... "      99             2
18      "... "      99           -1

I can manage simple SQL operations but this is beyond me. Is something like this even possible? If yes, how?

Upvotes: 1

Views: 43

Answers (2)

Randula Koralage
Randula Koralage

Reputation: 338

SELECT c.id,comment,c.video_id,SUM(v.vote) AS Vote_total 
FROM comments c, comments_votes v
WHERE c.id = v.comment_id
GROUP BY C.id, C.Comment, C.Video_ID;

Upvotes: 0

asmgx
asmgx

Reputation: 7984

select C.id, C.Comment, C.Video_ID, SUM(V.Votes) AS Vote_total 
from comments C 
left outer join comments_votes V 
on C.id=V.comment_id
group by C.id, C.Comment, C.Video_ID

Upvotes: 2

Related Questions