I have those two Tables:

tblCommentReactions

id	Liked	CommentID
1	0	1
2	1	1
3	1	1
4	0	2

1 is Like and 0 is dislike.

tblComments:

id	userID	message
1	1	message 1
2	1	message 2
3	2	message 1

I tried to select all comments and Count the dislikes and likes and give the result in the same Row.

SELECT c.ID as CommentID, c.message,
COUNT(case when Liked = 1 AND r.CommentID = c.ID then 1 else null end) as likes, 
COUNT(case when Liked = 0 AND r.CommentID = c.ID then 1 else null end) as dislikes
FROM tblcomments as c LEFT JOIN tblcommentreactions as r ON c.ID = r.CommentID
WHERE c.userID = 1;

Expected Output should be:

CommentID	message	likes	dislikes
1	message 1	2	1
2	message 2	0	1

On my Return it counts everything and only returns the first message. Could you tell me what i need to change in my request, to get my expected output?

CodePudding user response：

There are two issues in your query:

• you have no GROUP BY clause in presence of non-aggregated fields inside the SELECT clause, which will bring you have an error fired by the DBMS in the best case scenario, no error but random/subtle semantic errors in the worst one.
• you are attempting to filter your rows (the WHERE condition) before the aggregation is applied.

In order to solve:

• the first problem, you need to add the GROUP BY clause with the two missing selected fields, namely "c.ID" and "c.message"
• the second problem, you need to transform your current WHERE clause into an HAVING one (as long as this one applies after the aggregation has been carried out) and add the checked field, namely "c.userID", inside the GROUP BY clause, as long as it is a field that was selected along with the fields in the SELECT clause.

SELECT c.ID as CommentID, 
       c.message,
       COUNT(CASE WHEN Liked = 1 THEN 1 END) AS likes, 
       COUNT(CASE WHEN Liked = 0 THEN 1 END) AS dislikes
FROM      tblComments         AS c 
LEFT JOIN tblCommentReactions AS r 
       ON c.ID = r.CommentID
GROUP BY c.ID,
         c.message,
         c.userID
HAVING c.userID = 1

Minor fixes on the CASE construct that doesn't require "AND r.CommentID = c.ID" as already pointed in the comments section, but also the non-required "ELSE NULL" condition, that is considered by PostgreSQL as default for this construct.

Here's a demo in MySQL, though this should work in the most common DBMS' more or less.

CodePudding user response：

Try using group by clause e.g

select cr.CommentID ,c.message,
    COUNT(case when Liked = 1 AND cr.CommentID = c.ID then 1 else null end) as likes ,
    COUNT(case when Liked = 0 AND cr.CommentID = c.ID then 1 else null end) as dislikes
    
    from [tblCommentReactions] cr inner join tblComments c on cr.CommentID = c.id
    
    group by cr.CommentID , c.message

CodePudding user response：

SELECT c.Id,  COUNT(cr1.Liked), COUNT(cr2.Liked)
FROM Comments c
LEFT JOIN CommentReactions cr1 ON cr1.CommentId = c.Id AND cr1.Liked = 0
LEFT JOIN CommentReactions cr2 ON cr2.CommentId = c.Id AND cr2.Liked = 1
GROUP BY c.Id