Mysql : Selecting a monthly total occurence count

Question

In MySQL, I got a table similar to :

id  user_id date
1   1       2014-09-27
2   1       2014-11-05
3   1       2014-11-14
4   2       2014-12-03
5   1       2014-12-23

I would like to select the total monthly amount of people.

ExpectedOutput : 4

2014-09 = 1 user
2014-10 = 0 user
2014-11 = 1 user //user 1 is present twice in november, but I want him only once per month
2014-12 = 2 user

total expected = 4

So far, my request is :

SELECT count(id) 
FROM myTable u1 
WHERE EXISTS(
    SELECT id 
    FROM myTable u2 
    WHERE u2.user_id = u1.user_id
    AND DATE_SUB(u2.date, INTERVAL 1 MONTH) > u1.date
);

It ouput the correct amount, but on my (not so heavy) table, it take hours to execute. Any hints to make this one lighter or faster ?

Bonus :

Since INTERVAL 1 MONTH is not available in DQL, is there any way to do it with a Doctrine QueryBuilder ?

Answer 1

Try this!

It should give you exactly what you need...

        SELECT
          EXTRACT(YEAR FROM dates) AS the_year,
          EXTRACT(MONTH FROM dates) AS the_month,
          COUNT( DISTINCT user_id ) AS total
        FROM 
          myTable
        GROUP BY
          EXTRACT(YEAR FROM dates),
          EXTRACT(MONTH FROM dates);