'SQL Query Returning Wring values for Sum and Count

My SQL Query contains three tables the posts table, post_likes table, and comments table. All tables are connected with the post_id primary key in the posts table. I am trying to return the content of the posts row as well the amount of likes/dislikes it has in the post_likes table, and the amount of comments. The query worked fine until I introduced the second left join and it now displays the like_count column x5 dislike_count column x5 and the new comment_count x4.

This is the query in question:

SELECT c.post_id, c.post_name, c.post_content, c.post_datetime, c.user_name, sum(p.like_count) AS like_count, sum(p.dislike_count) AS dislike_count, sum(s.comment_count) AS comment_count FROM posts c LEFT JOIN post_likes p ON c.post_id = p.post_id LEFT JOIN comments s ON c.post_id = s.post_id WHERE c.user_name = 'test' GROUP BY c.post_id

is returning the sum values:

//column | returned value | expected value

    like_count | 10 | 2
    dislike_count | 5 | 1
    comment_count | 20 | 5

Some additional notes, the likes/dislikes are stored in the postlikes table with the structure.

post_like_id, like_count, dislike_count, post_id, user_name

The like or dislike count can only be 1 in either column the PHP handles this to ensure users cant like multiple times etc and the user_name column is the user who liked the post.

The comments table structure is as follows:

comment_id, comment_name, comment_content, comment_datetime, comment_count, post_id, user_name

The comment_count is always 1 when inserted to allow for the sum function, post_id is the id of the post for the comment, and the user_name is the user who commented.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source