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.
I want the output sorted starting with the current month listed first i.e October 2020 all the way back to November 2019.
Months November and December are from previous year i.e. 2019; so i would like to display the year next to the each month.
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.