MySQL group records by month does not show records as expected-CodePudding

I have written the following query but it does not group the records by month. Some of the records are displayed without order. I am unable to get the reason behind it.

select MONTHNAME(CREATE_TIME) AS MONTH,FEATURE as     
featureName,count(DISTINCT(FEATURE)) as featureCount, app_name as appName
from ceye.approve
where
    create_time >now() - INTERVAL 12 MONTH
 AND
 review_time is NULL

group by MONTH,FEATURE,appName

ORDER BY MONTH desc ;

The data displayed by it is as follows:

Here is the data in image for reference

October,Replace PAN Card,1,Retail Bank Portal    
October,S3,1,Retail Bank Portal    
October,View Account Summary,1,Retail Bank Portal    
November,login,1,Retail Bank Portal    
November,Replace PAN Card,1,Retail Bank Portal    
March,login,1,Retail Bank Portal    
March,update profile,1,Retail Bank Portal    
January,FI unfreeze PAN Card,1,Retail Bank Portal    
January,login,1,Retail Bank Portal    
January,Replace PAN Card,1,Retail Bank Portal    
December,login,1,Retail Bank Portal    
December,Replace PAN Card,1,Retail Bank Portal      
December,update profile,1,Retail Bank Portal    
December,update user profile api,1,Retail Bank Portal     
April,login,1,Mobile Banking    
April,login,1,Retail Bank Portal    
April,update profile,1,Markets Research Portal    
April,update profile,1,Retail Bank Portal

CodePudding user response：

When you would like to sort by the months according to their order in the calender, you have to order by their number. You can achieve this by using MONTH. In this way, you can get the month number for the given dates and do the sort:

ORDER BY MONTH(creation_time) DESC

Here is an example that shows it's working fine: db<>fiddle

CodePudding user response：

Research order by case as an example -

DROP TABLE IF EXISTS T;

CREATE TABLE T(DT date, val int);

INSERT INTO T VALUES
('2022-01-01',10),
('2022-02-01',10),('2022-02-01',10),
('2022-03-01',10),('2022-03-01',10),('2022-03-01',10),
('2022-05-01',10),('2022-05-01',10),('2022-05-01',10),('2022-05-01',10),('2022-05-01',10);

select monthname(dt),sum(val)
from t
group by monthname(dt)
order by
case when monthname(dt) = 'December' then 1
      when monthname(dt) = 'November' then 2
      when monthname(dt) = 'October'  then 3
      when monthname(dt) = 'September' then 4
      when monthname(dt) = 'August'   then 5
      when monthname(dt) = 'July'     then 6
      when monthname(dt) = 'June'     then 7
      when monthname(dt) = 'May'      then 8
      when monthname(dt) = 'April'    then 9
      when monthname(dt) = 'March'    then 10
      when monthname(dt) = 'February' then 11
      when monthname(dt) = 'January'  then 12
end
;

 --------------- ---------- 
| monthname(dt) | sum(val) |
 --------------- ---------- 
| May           |       50 |
| March         |       30 |
| February      |       20 |
| January       |       10 |
 --------------- ---------- 
4 rows in set (0.002 sec)