How to count the number of records in MySQL by month for the last 12 months including the current month?

Question

I have found several answers for questions similar to this one, but none of then exclusive for MySQL. I have a table like this

+-----------+---------------------+
| requestor | request_date        |
+-----------+---------------------+
| email1    | 2015-08-02 01:03:51 |
----------------------------------
| email2    | 2015-08-01 11:24:32 |
-----------------------------------
| email3    | 2015-07-01 07:35:32 |
-----------------------------------
| email4    | 2015-06-01 16:24:32 |
-----------------------------------
| ...       | ...                 |
-----------------------------------
| email100  | 2014-09-01 22:45:04 |
+-----------+---------------------+

I need and output:

+----------+-------+
| Months   | Count |
+----------+-------+
| Aug-2015 | 2     |
--------------------
| Jul-2015 | 1     |
--------------------
| Jun-2015 | 1     |
--------------------
| ...      | ...   |
--------------------
| Sep-2014 | 1     |
+----------+-------+

I tried with this code

SELECT *
 FROM(SELECT DATE_FORMAT(now(), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 3 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 4 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 5 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 6 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 7 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 8 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 9 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 10 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 11 MONTH), "%b %Y") as Month) AS Months
  LEFT JOIN (SELECT DATE_FORMAT(utilization.request_time, "%b %Y") AS Month,      requestor, COUNT(request_time)
        FROM utilization 
        WHERE utilization.request_time > DATE_SUB(now(), INTERVAL 12 MONTH)
        GROUP BY request_time) AS Datas
     ON Months.Month = Datas.Month

I believe the asker wants 12 rows even if there may not be data for some months, that is why the unions are used. I suggest using `UNION ALL` for those. — Paul Maxwell, Aug 07 '15 at 05:17

Joshua K · Accepted Answer · 2015-08-07T05:03:45.743

1

SELECT CONCAT(SUBSTRING(DATE_FORMAT(`request_date`, '%M'),1,3),DATE_FORMAT(`request_date`,'-%Y')) as dateName, COUNT(*)
FROM foo
WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(`request_date`, '%Y%m'))<12
GROUP BY YEAR(`request_date`), MONTH(`request_date`)

Result:

dateName    COUNT(*)
Sep-2014    1
Jun-2015    1
Jul-2015    1
Aug-2015    2

edited Aug 07 '15 at 05:03

answered Aug 07 '15 at 04:58

Joshua K

2,407
1
10
13

Hi Joshua. I got the correct answer, but it goes until Jul-2014, it doesn't stop on Sep-2014 – Felix D. Aug 07 '15 at 05:09
Hi Felix, I have no entries smaler than Sep and greater than Jun. So they are not listed in my example. If you have the entries in your database you will get them in the resultset. – Joshua K Aug 07 '15 at 05:13
Yes, I am getting it. Is there any way to truncate the result in Month 12 before my current month? – Felix D. Aug 07 '15 at 05:33
1

it is. take a look at the WHERE-clause. only results where the difference between the actual month and the request_date-month is less than 12 month are shown. – Joshua K Aug 07 '15 at 05:38
I got the result only until Sep-2014. Now, to show those in between that has no values with value 0. That is why I was trying to put the UNIONs. – Felix D. Aug 07 '15 at 05:57
Ah now I got the point. This is not the task of the database layer. It should be part of the presentation layer of your application. If you need it inside the select-query (I would not prefer this. Better split your application in layers) you have to create a table with all the values in this range. There is no need to exist physicly for this table. You can create a SELECT statement which uses NOW() and DATESUB to create the 12 month-year-combinations. Then make a RIGHT/LEFT Join to this resultset and you get what you wanted. – Joshua K Aug 07 '15 at 06:17
I will follow your recommendation. The answer for the SQL code is perfect, I can work with that. Thank you so much – Felix D. Aug 07 '15 at 06:27

score 0 · Answer 2 · edited Aug 07 '15 at 05:02

0

SELECT requestor, id
FROM tbl_data JOIN
(
    SELECT COUNT(requestor), id
    FROM tbl_data
    GROUP BY DATE_FORMAT(request_date,'%y-%m')
) AS temp
ON temp.id = tbl_data.id

edited Aug 07 '15 at 05:02

Tim Biegeleisen

502,043
27
286
360

answered Aug 07 '15 at 04:46

Chintan7027

7,115
8
36
50

score 0 · Answer 3 · edited Aug 07 '15 at 04:58

0

Try this:

SELECT MAX(EXTRACT(YEAR_MONTH FROM request_date)) AS Months, COUNT(*) AS Count
FROM table
WHERE request_date > '2015-01-01 01:01:01'
GROUP BY EXTRACT(YEAR_MONTH FROM request_date);

edited Aug 07 '15 at 04:58

Tim Biegeleisen

502,043
27
286
360

answered Aug 07 '15 at 04:48

user1133275

2,642
27
31

How to count the number of records in MySQL by month for the last 12 months including the current month?

3 Answers3

Linked