title: SQL query to get count of likes, comments, reads and score for each post
body: <p>So I have 4 tables:</p>
  <ol>
  <li>post</li>
  <li>likes</li>
  <li>comments</li>
  <li>reads</li>
  </ol>
  <p>Each table has the column <code>post_id</code></p>
  <p>I want to create a query, where for each post, i want to know the number of likes, the number of comments, and the number of reads <br>
  Finally, i want another column caled score, where i sum up the values in likes, comments and reads</p>
  <p>Here is the sql query i have to do that:</p>
  <pre><code>SELECT posts.post_id,
         posts.title,
         count(likes.like_id) as likes,
         count(comments.comment_id) as comments,
         post_read_count.count as reads,
         (count(likes.like_id) + count(comments.comment_id) + post_read_count.count) as score
  FROM community.posts
  LEFT JOIN community.likes
      ON posts.post_id = likes.post_id
  LEFT JOIN community.comments
      ON posts.post_id = comments.post_id
  LEFT JOIN community.post_read_count
      ON posts.post_id = post_read_count.post_id
  WHERE posts.is_deleted = false
  GROUP BY posts.post_id, post_read_count.count
  ORDER BY posts.post_id DESC
  LIMIT 100
  </code></pre>
  <p>but i am unable to get the correct number of comments and the sum of items into score is not working properly</p>
  <p>I am on postgres</p>
  <p>heres a sample data:</p>
  <pre><code>posts:
  post_id | title
  101       some title
  102       hello there
  103       good day sir
  104       good bye         
  105       whats up 
  
  
  likes:
  like_id | post_id
  1         101  
  2         101
  3         101
  4         102
  5         102
  6         104
  7         104
  8         105
  9         105
  10        101
  
  comments:
  comment_id | post_id
  1            103  
  2            103
  3            103
  4            101
  5            102
  6            104
  7            105
  8            105
  9            105
  10           103
  
  post_read_count:
  post_id | count
  101       12
  102       54
  103       76
  104       23         
  105       87 
  </code></pre>
  <p>desired output:</p>
  <pre><code>output:
  post_id | title         | likes | comments | reads | score
  101       some title      4       1          12      17
  102       hello there     2       1          54      57
  103       good day sir    0       4          76      80
  104       good bye        2       1          23      26
  105       whats up        2       3          87      92
  </code></pre>