The order by months and years in sql with the sum

Question

I have a stored procedure, and a select statement:

SELECT { fn MONTHNAME(OrderDate) } AS MonthName, YEAR(OrderDate) AS Year, SUM(TotalValue) AS Profits FROM [Order] WHERE (YEAR(OrderDate) = @year) GROUP BY { fn MONTHNAME(OrderDate) }, YEAR(OrderDate) 

this shows the total amount for each month But I need to order the result by months and years, because my result is shown as follows:

 April 2013 February 2013 January 2013 June 2013 March 2013 May 2013 

What is the solution then?

Answer 1

Try the following:

 SELECT { fn MONTHNAME(OrderDate) } AS MonthName, YEAR(OrderDate) AS Year, SUM(TotalValue) AS Profits FROM [Order] WHERE (YEAR(OrderDate) = @year) GROUP BY { fn MONTHNAME(OrderDate) }, MONTH(OrderDate), YEAR(OrderDate) order by Year(orderDate),month(OrderDate) 

Please note that you need to add any fields that you order to the group on offer

Answer 2

I think you should use the order by clause at the end

 SELECT { fn MONTHNAME(OrderDate) } AS MonthName, YEAR(OrderDate) AS Year, SUM(TotalValue) AS Profits FROM [Order] WHERE (YEAR(OrderDate) = @year) GROUP BY { fn MONTHNAME(OrderDate) }, YEAR(OrderDate) ORDER BY { fn MONTH(OrderDate) }, YEAR(OrderDate) 

or

You can do it

 SELECT CASE { fn MONTH(OrderDate) } when 0 then 'JAN' when 1 then 'FEB' when 2 then 'MAR' when 3 then 'APR' when 4 then 'MAY' when 5 then 'JUN' when 6 then 'JUL' when 7 then 'AUG' when 8 then 'SEP' when 9 then 'OCT' when 10 then 'NOV' when 11 then 'DEC' END AS MonthName, YEAR(OrderDate) AS Year, SUM(TotalValue) AS Profits FROM [Order] WHERE (YEAR(OrderDate) = @year) GROUP BY { fn MONTH(OrderDate) }, YEAR(OrderDate) ORDER BY { fn MONTH(OrderDate) }, YEAR(OrderDate) 

Answer 3

If you want to order as Jan, Feb, March, use

 SELECT month(OrderDate) MonthName, year(OrderDate) Year, sum(TotalValue) Profits FROM [Order] WHERE month(OrderDate) = @year ORDER BY year(OrderDate), month(OrderDate) GROUP BY year(OrderDate), month(OrderDate) 

Answer 4

Just add

 Order by max(OrderDate) 

in the end.

 SELECT { fn MONTHNAME(OrderDate) } AS MonthName, YEAR(OrderDate) AS Year, SUM(TotalValue) AS Profits FROM [Order] WHERE (YEAR(OrderDate) = @year) GROUP BY { fn MONTHNAME(OrderDate) }, YEAR(OrderDate) Order by max(OrderDate) 

Now about how this works:

If you order by months, years separately, they will arrive in ascending order in alphabetical order (April to January). If you order by order, then the date identifier will be ordered based on the date value, which, of course, is ordered by month / year.

Answer 5

Use the following query it will work for you.

 SELECT { fn MONTHNAME(OrderDate) } AS MonthName, datepart(MM,OrderDate) AS Month, YEAR(OrderDate) AS Year, SUM(TotalValue) AS Profits FROM [Order] WHERE (YEAR(OrderDate) = @year) GROUP BY { fn MONTHNAME(OrderDate) }, YEAR(OrderDate), datepart(MM,**OrderDate**) ORDER BY datepart(MM,**OrderDate**) 

Answer 6

 select cast(year(appointmentdate) as char(4))+''+ case when len(cast(month(appointmentdate)as char(2)))=1 then '0'+cast( month(appointmentdate) as char(2) ) else cast(month(appointmentdate) as char(2))end as apporder from timeslots group by appointmentdate order by appOrder 

Answer 7

Try it. It works great. Please note that you are already entering the year value. Therefore, you do not need to order or group the Year, as you can only choose one year at a time. Therefore, additional code is not needed.

 SELECT { fn MONTHNAME(OrderDate) } AS MonthName, SUM(TotalValue) AS Profits FROM [Order] WHERE (YEAR(OrderDate) = @year) GROUP BY { fn MONTHNAME(OrderDate) }, DATEPART(M, OrderDate) ORDER BY DATEPART(M, OrderDate)