count record according to month for all user need mysql query

I have 2 tables:

user

id username
1    a
2    b

transaction

id user_id transaction_date
1    1       2016-02-02 11:23:02
2    1       2016-03-02 11:23:02
3    2       2016-02-02 11:23:02

I want record like this:

id user_id Jan Feb mar .......
1   1       0   1   1
2   2       0   1   0

Let’s first get the result without the auto increment id:

SELECT 
    user_id, 
    SUM(IF(MONTH(transaction_date)=1), 1, 0) AS Jan,
    SUM(IF(MONTH(transaction_date)=2), 1, 0) AS Feb,
    SUM(IF(MONTH(transaction_date)=3), 1, 0) AS Mar,
    .
    .
FROM transaction
GROUP BY user_id

In this query, you will add 1 to the column that correspond to the month of the transaction_date field and add 0 to all other fields.

If you want to add the auto increment ID in the query, it gets complex. You’d rather create a table with the required fields and the auto inc field, and insert into it the result of the previous query.

HTH