MySql: Count occurrences of distinct values by date

Question

I am trying to find a MySQL query that will find distinct values in a particular field (ID) with a specific date, and then count the number of occurrences of that value and separate it by date. See example below:

example db

id         Date                    ID
-----      ------                  -------
1          2015-01-24 14:13:50     user1
2          2015-01-24 14:13:50     user1
3          2015-01-24 14:13:50     user2
4          2015-01-24 14:13:50     user2
5          2015-02-24 04:13:50     user2
6          2015-02-24 04:13:50     user1
7          2015-02-24 04:13:50     user3

expected output

month        uniqueUsers
---------    ----------
12015        2
22015        3

this query

SELECT DISTINCT CONCAT( MONTH(  `Date` ) , YEAR(  `Date` ) ) AS 
MONTH ,  COUNT( DISTINCT  `ID` )  AS uniqueUsers
FROM  `myDB`

gives me these results:

month        uniqueUsers
---------    ----------
12015        2

but it wont provide data for month 2

What am I missing here?

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-02-01T22:07:48

You are missing group by and aggregation functions:

SELECT CONCAT(MONTH(`Date) , YEAR(`Date`)) AS MYYYY , 
       COUNT(DISTINCT ID) as NumUsers
FROM  `myDB` 
GROUP BY CONCAT(MONTH(`Date`), YEAR(`Date`) )

As a note. I think it is better to put year-month combinations in the YYYYMM format. This makes it nicer to sort the data. You can write this as:

SELECT DATE_FORMAT(`Date`, '%Y-%m') as YYYYMM, COUNT(DISTINCT ID) as NumUsers
FROM  `myDB` 
GROUP BY YYYYMM
ORDER BY YYYYMM;

In fact, you could just leave it as two columns:

SELECT YEAR(`Date`) as YYYY, MONTH(`Date`) as MM,
       COUNT(DISTINCT ID) as NumUsers
FROM  `myDB` 
GROUP BY YYYY, MM
ORDER BY YYYY, MM;

MySql: Count occurrences of distinct values by date

1 Answers