Charles Bao Charles Bao - 3 months ago 12
MySQL Question

how to count how many users and how many comments to each post in mysql?

Here is my comments table:

|id(int) | user_id(int) | post_id(int) | text(char)|
----------------------------------------------------
| | | | |


I want to calculate how many users has comments to each post
and how many comments do every post have.

I've already done the latter one as following:

select count(*) from comments group by post_id;


but how about the first one? any idea?

Answer

How many users has comments to every post

SELECT post_id, count(distinct user_id) as user_count 
FROM comments
GROUP BY post_id

This way you are avoiding subqueries.