Format grouped date as readable date

Question

So I have a query that correctly displays the number of registrations for the last 12 months. Here is display: Registrations per month for last 2 years

1--17    
2--12    
3--17    
4--8    
5--9    
6--8    
7--15    
8--20    
9--12    
10--14    
11--13    
12--14

But since im running this in say June, the last mont I need to say the readable date May and not '1'. I want instead:

May--17
Apr--12
March--17
.
.
.

Here is my current MYSQL:

SELECT MONTH(create_date) as month , COUNT(create_date) as count 
FROM `users` 
WHERE create_date >= NOW() - INTERVAL 1 YEAR 
GROUP BY MONTH(create_date)

I assumed I just have to use FORMAT_DATE() on the GROUP By as:

GROUP BY FORMAT_DATE(MONTH(create_date, '%M'))

And that would give me my readable month, but the sql statement reports it is not correct. Anyone know how to accomplish this?

Answer 1

Try this:

SELECT DATE_FORMAT(create_date, '%M') AS month, COUNT(create_date) AS count
FROM users
WHERE create_date >= NOW() - INTERVAL 1 YEAR
GROUP BY MONTH(create_date);

The result will be:

+-----------+-------+
| month     | count |
+-----------+-------+
| January   |     1 |
| February  |     1 |
| March     |     1 |
| April     |     1 |
| May       |     2 |
| June      |     2 |
| July      |     1 |
| August    |     1 |
| September |     1 |
| November  |     1 |
| December  |     1 |
+-----------+-------+

Answer 2

You can use STR_TO_DATE() to convert the number to a date, and then back with MONTHNAME()

SELECT MONTHNAME(create_date(6, '%m')) as month , COUNT(create_date) as count 
FROM `users` 
WHERE create_date >= NOW() - INTERVAL 1 YEAR 
GROUP BY MONTH(create_date)