MySQL group_concat() month wise ordering

Question

I have a mysql table with date column named MonthYear which contains dates from March 2013 to Jan 2014 in yyyy-mm-dd format. I trying to group_concat() the that column as,

select
GROUP_CONCAT(DISTINCT 
           CONCAT(' ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, ''%b %y'')=''',
                   DATE_FORMAT(ms.MonthYear, '%b %y'),
                   ''' THEN Count ELSE 0 END)) AS ''', 
                   DATE_FORMAT(ms.MonthYear, '%b %y'), '''' 
                 )
             )
INTO @sql from mysamp ms order by ms.MonthYear

But the concated result is,

ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, '%b %y')='Sep 13' THEN Count ELSE 0 END)) AS 'Sep 13', 
ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, '%b %y')='Oct 13' THEN Count ELSE 0 END)) AS 'Oct 13', 
ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, '%b %y')='Jun 13' THEN Count ELSE 0 END)) AS 'Jun 13', 
ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, '%b %y')='Jul 13' THEN Count ELSE 0 END)) AS 'Jul 13', 
ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, '%b %y')='Dec 13' THEN Count ELSE 0 END)) AS 'Dec 13', 
ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, '%b %y')='May 13' THEN Count ELSE 0 END)) AS 'May 13', 
ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, '%b %y')='Aug 13' THEN Count ELSE 0 END)) AS 'Aug 13', 
ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, '%b %y')='Nov 13' THEN Count ELSE 0 END)) AS 'Nov 13', 
ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, '%b %y')='Jan 14' THEN Count ELSE 0 END)) AS 'Jan 14', 
ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, '%b %y')='Apr 13' THEN Count ELSE 0 END)) AS 'Apr 13', 
ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, '%b %y')='Mar 13' THEN Count ELSE 0 END)) AS 'Mar 13'

Why the concated result comes in disordered format?

Answer 1

In your query you have no GROUP BY clause, that means that your group effectively becomes all rows, and you get a single resulting row with the grouped output.

The ORDER BY in your current statement is applying to the result set, however you only have a single row in the result, so the order is not really doing anything useful there.

To order the rows being input into the GROUP_CONCAT function, you can specify an ORDER within the parameters to the GROUP_CONCAT function itself, much the same as you currently specify DISTINCT.

See the documentation here: http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat

This should do what you need:

select
GROUP_CONCAT(DISTINCT
           CONCAT(' ROUND(SUM(CASE WHEN DATE_FORMAT(MonthYear, ''%b %y'')=''',
                   DATE_FORMAT(ms.MonthYear, '%b %y'),
                   ''' THEN Count ELSE 0 END)) AS ''', 
                   DATE_FORMAT(ms.MonthYear, '%b %y'), '''' 
                 )  
              ORDER BY ms.MonthYear)
INTO @sql from mysamp ms