CODEWITHSUNDEEP
mysqlsqlphpmyadminsql-order-by
wbrandt
wbrandt

Reputation: 149

SQL Order By month name chronologically instead of alphabetically

I would like to get the month names to be in chronological order instead of alphabetically ordered. Here is my Sql code.

SELECT month, sum(total) 
FROM (SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total  
      FROM projects 
      WHERE terms >= '2017/01/01'
      GROUP BY MONTH(terms) 
      UNION 
      SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total 
      FROM archive
      WHERE terms >= '2017/01/01' 
      GROUP BY MONTH(terms) 
     ) AS test
GROUP BY month
ORDER BY month

The output of the code above looks like This

I would like it to be:

January

February

March

...

...

Upvotes: 0

Views: 10026

Answers (3)

N.Dov
N.Dov

Reputation: 66

If you're using SQL Server database:

SELECT month, sum(total) 
FROM (SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total  
      FROM projects 
      WHERE terms >= '2017/01/01'
      GROUP BY MONTH(terms) 
      UNION 
      SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total 
      FROM archive
      WHERE terms >= '2017/01/01' 
      GROUP BY MONTH(terms) 
     ) AS test
GROUP BY month
ORDER BY month(month + ' 1 2014')

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

Order the month as int to order it. With MONTH(STR_TO_DATE(month, '%M')) 

SELECT month, sum(total) 
    FROM (SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total  
          FROM projects 
          WHERE terms >= '2017/01/01'
          GROUP BY MONTH(terms) 
          UNION 
          SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total 
          FROM archive
          WHERE terms >= '2017/01/01' 
          GROUP BY MONTH(terms) 
         ) AS test
    GROUP BY month
    ORDER BY MONTH(STR_TO_DATE(month, '%M'))

Upvotes: 2

user7392562
user7392562

Reputation:

You can leverage the temp table

with temp as(
SELECT month, sum(total) as total
FROM (SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total  
      FROM projects 
      WHERE terms >= '2017/01/01'
      GROUP BY MONTH(terms) 
      UNION 
      SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total 
      FROM archive
      WHERE terms >= '2017/01/01' 
      GROUP BY MONTH(terms) 
     ) AS test
GROUP BY month
)
select * from temp order by month

Upvotes: 0

Related Questions