How to count how many entries per month?

Question

MySQL

Guys, tell me, you can display the result in pure sql, see the original table:

user_id | created_at
1       | 123456
1       | 123456
2       | 123456
2       | 123456
3       | 123456
3       | 123456
3       | 123456
3       | 123456

At the output, I want to calculate how many records I have with id by month, based on the table above

user_id | count | month
1       | 2     | 12
2       | 2     | 5
3       | 4     | 3

Or will you still have to implement it in the puff

Answer 1

I'm not sure exactly for mysql, but I would write like this. It seems to me not a good idea to use the reserved words count and month as field names, but I wrote as asked (-:

SELECT
    user_id,
    COUNT(1) as count,
    MONTH(FROM_UNIXTIME(created_at)) as month
FROM
    my_table
GROUP BY
    user_id,
    MONTH(FROM_UNIXTIME(created_at))