'MySQL group records by month does not show records as expected

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:

Month Feature SUM App_Name
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
mysqlsql-order-by


Solution 1:[1]

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

Solution 2:[2]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jonas Metzler
Solution 2 P.Salmon

Related Questions