Charles Bao Charles Bao - 7 months ago 57
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?


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.