Charles Bao Charles Bao - 1 year ago 41
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 Source

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.