CODEWITHSUNDEEP
mysqlsqlselectcount
user2798694
user2798694

Reputation: 1033

SQL that counts from another table

I am managing a database for a social network where users can like, comment and I created a table each for the post, like, and comments. 

like --- post_id
         user_id

comment --- post_id
            user_id

Now when I run select * from post, I want to be able to add a two columns likes and comments that counts the number of likes and comments having each post_id.

How can I do this?

Upvotes: 4

Views: 2599

Answers (4)

Richard
Richard

Reputation: 30628

You will need to join to both tables.

SELECT post.*, COUNT(likes.id) AS like_count, COUNT(comment.id) AS comment_count
FROM post
    LEFT JOIN likes ON post.id = likes.post_id
    LEFT JOIN comment ON post.id = comment.post_id
GROUP BY post.id

Upvotes: 6

carexcer
carexcer

Reputation: 1427

SELECT post.id, COUNT(like.id) AS like_count, COUNT(comment.id) AS comment_count
FROM post p
    LEFT JOIN like ON post.id = like.post_id
    LEFT JOIN comment ON post.id = comment.post_id
GROUP BY post.id

It's like answer of @Richard but modifying the select, because the column in group by must be in the select clause.

Upvotes: 4

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727077

Like this:

SELECT *
,    (SELECT COUNT(*) FROM likes WHERE post_id=p.id) as LIKE_COUNT
,    (SELECT COUNT(*) FROM comment WHERE post_id=p.id) as COMMENT_COUNT
FROM post p

Demo on sqlfiddle.

Upvotes: 6

Eugen Rieck
Eugen Rieck

Reputation: 65342

SELECT
  post.*,
  IFNULL(COUNT(like.user_id),0) AS likecount,
  IFNULL(COUNT(comment.user_id),0) AS commentcount
FROM
  post
  LEFT JOIN like ON post.post_id=like.post_id
  LEFT JOIN comment ON post.post_id=comment.post_id
GROUP BY
  like.post_id

Upvotes: 3

Related Questions