Counting Grouped Rows By Where Clause MySQL-CodePudding

I want to count the amount of people liked a post where a certain user liked that post.

Post:
Author - INT
Content - Text

Post_Like:
User_ID - Int
Post_ID - Int

SELECT p.content, count(pl.User_ID) FROM Post p JOIN Post_Like pl ON p.id = pl.Post_ID WHERE pl.User_ID = 40 group by pl.Post_ID

This is returning count 1 for each, which is wrong, but I am not sure how to count all the likes instead of only where the ID matches.

CodePudding user response：

You can use:

SELECT p.content, count(pl.User_ID) 
FROM Post p 
  JOIN Post_Like pl ON p.id = pl.Post_ID 
WHERE pl.Post_ID IN (
  SELECT u.Post_ID
  FROM Post_Like u
  WHERE u.User_ID = 40
)
GROUP BY p.content

Note that you need to list non-aggregate columns from SELECT in GROUP BY (ONLY_FULL_GROUP_BY sql mode).