Charles Bao Charles Bao - 3 months ago 11
MySQL Question

how to count how many users and how many comments to every 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 every 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.

Comments