CODEWITHSUNDEEP
mysql
Felix D.
Felix D.

Reputation: 133

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

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

Upvotes: 0

Views: 503

Answers (3)

Joshua K
Joshua K

Reputation: 2537

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

Upvotes: 1

Chintan7027
Chintan7027

Reputation: 7605

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

Upvotes: 0

user1133275
user1133275

Reputation: 2735

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

Upvotes: 0

Related Questions