SQL COUNT return multiple rows

I have those two Tables:

tblCommentReactions

1 is Like and 0 is dislike.

tblComments:

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:

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?

>Solution :

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.