How to count in a join statement

Question

I have got table post: int post_id, varchar title, text content
And table comment:int comment_id, int post_id, varchar content where post_id is a foreign key references table post.

How do i get the post_id and sum of comments of each post order by comments count. Thank you.

Answer 1

If you want posts that have no comments:

SELECT
    post.post_id,
    --post.title,
    --post.content,
    COUNT(comment.post_id) AS comment_count
FROM post
LEFT JOIN comment ON post.post_id = comment.post_id
GROUP BY post.post_id
ORDER BY comment_count DESC

(This query uses the MySQLs GROUP BY with hidden columns extension).

If you don't want posts that have no comments you can use a simpler query:

SELECT post_id, COUNT(*) AS comment_count
FROM comment
GROUP BY post_id
ORDER BY comment_count DESC