Postgres return the total number of rows only once

Question

I have 2 tables, a posts table and a comments table. I'm trying to select all the comments to a post, I already have a query for this which works. I've simplified the query to just return the comment id.

simplified posts table:

post_id	username
1	'randomuser'
2	'randomuser1'

simplified comments table:

comment_id	post_id	content
1	1	'cool post'
2	2	'cool post1'

The query looks like:

SELECT comment_id, 
 (SELECT COUNT(*) FROM comments WHERE comments.comment_id = comment_id) as comment_count 
FROM comments WHERE post_id = 1;

This query will return all the comments that equal a specific post id and will return the total count of all comments. The problem is that this query is returning the total number of comments to a post with every comment.

ie:

comment_id	comment_count <- this is being returned for every comment, I'm trying to return it only once
1	2
2	2

The problem is in my query as I specify to get the count of comments for every comment however I'm trying to only return the comment_count only once. As I'm writing this I think it would make more sense to just make another query for the comment count however I'm curious if there is a way.

Answer 1

You need group by. I'm not sure about all your column names. Try

SELECT
  comments.comment_id
  SUM(likes.likes) NoLikes,
  SUM(comments.comment_count) comments
FROM
  likes 
JOIN
  comments  on likes.comment_id = comments.comment_id
GROUP BY 
  comments.comment_id;