CODEWITHSUNDEEP
mysql
user1715025
user1715025

Reputation: 53

Selecting posts that have at least one comment

Basically I want to select all the posts that have at least one comment and get the comment count for each. What I cam up with so far is only giving me one result, which is has an accurate count but there are more than one posts that have comments. Can any recommend changes to make this work

SELECT
    posts.id,
    COUNT(DISTINCT comments.post_id) AS count
FROM
    posts
LEFT JOIN comments ON posts.id = comments.post_id

Upvotes: 0

Views: 260

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You need a group by statement. And you can change the join to an inner join because you want only posts with comments:

SELECT p.id, COUNT(*) AS count
FROM posts p INNER JOIN
     comments c
     ON p.id = c.post_id
GROUP BY p.id;

The expression count(distinct c.post_id) will return 1 for each row, because there is only one distinct post id for each row. COUNT(*) will get the number of comments.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Using aggregate functions you must group them see here GROUP BY (Aggregate) Functions for the posts must contain atleast one comment you can use HAVING count >= 1

SELECT
    posts.id,
    COUNT(DISTINCT comments.post_id) AS `count`
FROM
    posts
LEFT JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id
HAVING `count` >= 1

Upvotes: 2

Related Questions