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);

Answer 2

SELECT COUNT(DISTINCT user_id), YEAR(date) + '-' + MONTH(date)
FROM MyTable
GROUP BY YEAR(date), MONTH(date)

Answer 3

For you problem, what I would do is :

• Creating a subrequst grouping the distinct sum of people by month
• Creating a request making the sum of the sub-result.

Here is a working example (with your datas) sqlFiddle

And here is the request :

SELECT SUM(nb_people)
FROM (
-- This request return the number of distinct people in one month.
SELECT count(distinct(user_id)) AS nb_people, MONTH(`date`), YEAR(`date`)
FROM test
GROUP BY MONTH(`date`)
) AS subQuery
;