CODEWITHSUNDEEP
sql-server-2012
Benjo
Benjo

Reputation: 95

SQL Server 2012: How to order by year then month in chronological order

I have a simple query that averages numbers and breaks them down by year and month. The problem I am having is I can't find a way to order the results by year then month....in chronological order. Here is an example of my results;

    Month       Year    AverageAmount
    ---------------------------------
    April       2012    167582.1139
    August      2012    206124.9323
    December    2012    192481.8604
    February    2012    227612.0485
    January     2012    214315.2187
    July        2012    195320.075
    June        2012    196174.3195
    March       2012    201199.9894
    May         2012    190526.0571
    November    2012    203441.5135
    October     2012    216467.7777
    September   2012    217635.9174
    April       2013    206730.5333
    August      2013    197296.0563

As you can see in the table above, the months are in alphabetical order... what I need is the results to be in chronological order, ie...

    Month       Year    AverageAmount
    ----------------------------------
    January     2012    214315.2187
    February    2012    227612.0485
    March       2012    201199.9894
    April       2012    167582.1139
    May         2012    190526.0571
    June        2012    196174.3195
    April       2012    206730.5333
    July        2012    195320.075
    August      2012    206124.9323
    September   2012    217635.9174
    October     2012    216467.7777
    November    2012    203441.5135
    December    2012    192481.8604
    January     2013    187268.3027
    February    2013    179755.0666
    March       2013    200131.6533

Query:

SELECT 
    datename(month, col1) Month,
    year(col2) Year,
    avg(col3) AverageAmount
FROM 
    myDB
GROUP BY 
    datename(month, datefunded), year(datefunded)
ORDER BY 
    year(datefunded), datename(month, datefunded)

Any help would be greatly appreciated

Upvotes: 0

Views: 6066

Answers (2)

Lars Baunwall
Lars Baunwall

Reputation: 75

Have a look at this answer: Convert month name to month number in SQL Server

Maybe that solves the sorting for you? You can use one of the suggestions to convert the month to a number, so that e.g. March > 3. Then you sort by that.

Upvotes: 0

marc_s
marc_s

Reputation: 754488

Just use this ORDER BY:

ORDER BY 
    YEAR(datefunded), MONTH(datefunded)

That'll sort your data by the numerical values of year and month of that datefunded

Upvotes: 2

Related Questions