mysqlsqlselectcountyearmonth

Get month-wise count of records in mySQL starting with the current month

Below is my mysql table:

mysql> DESCRIBE mytable;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| url_timestamp | timestamp    | NO   |     | NULL    |       |
| cr_num        | varchar(50)  | NO   |     | NULL    |       |
| status        | varchar(255) | NO   |     | NULL    |       |
| rollback_date | timestamp    | YES  |     | NULL    |       |
| rollback_user | varchar(255) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

I wish to get the month-wise count for the cr_num which is primary key for the past 12 months.

mysql> SELECT MONTHNAME(url_timestamp) , COUNT(url_timestamp) FROM mytable WHERE status='PRODUCTION' and url_timestamp >= NOW() - INTERVAL 1 YEAR GROUP BY MONTHNAME(url_timestamp);
+--------------------------+----------------------+
| MONTHNAME(url_timestamp) | COUNT(url_timestamp) |
+--------------------------+----------------------+
| November                 |                   43 |
| December                 |                   69 |
| January                  |                  220 |
| October                  |                  225 |
| February                 |                  209 |
| March                    |                  123 |
| April                    |                   93 |
| May                      |                  113 |
| June                     |                  217 |
| July                     |                  129 |
| August                   |                  185 |
| September                |                  415 |
+--------------------------+----------------------+
12 rows in set (0.01 sec)

There are three issues with the output i get.

  1. I want the output sorted starting with the current month listed first i.e October 2020 all the way back to November 2019.

  2. Months November and December are from previous year i.e. 2019; so i would like to display the year next to the each month.

  3. I would like to get the data only for this Year 2020 thus, November and December records should not show.

I'm not from the database background, so I did not deep dive too much into what I could have tried.

Solution

  • You can use functions LAST_DAY() to group by month and DATE_FORMAT() to format the month with the year:

    SELECT DATE_FORMAT(LAST_DAY(url_timestamp), '%M %Y') month,
       COUNT(url_timestamp) counter
FROM mytable 
WHERE status='PRODUCTION' and url_timestamp >= NOW() - INTERVAL 1 YEAR 
GROUP BY month;

    To get the rows sorted correctly you need to also GROUP BY LAST_DAY(url_timestamp) so you can use it in the ORDER BY clause:

    SELECT DATE_FORMAT(LAST_DAY(url_timestamp), '%M %Y') month,
       COUNT(url_timestamp) counter
FROM mytable 
WHERE status='PRODUCTION' and url_timestamp >= NOW() - INTERVAL 1 YEAR 
GROUP BY month, LAST_DAY(url_timestamp)
ORDER BY LAST_DAY(url_timestamp);