Commander Par mois et année en sql avec sum

Essaye ça:

 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) 

Notez que vous devez append tous les champs que vous commandz à la clause group by

Si vous voulez order en janvier, février, mars

 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) 

Je pense que vous devriez utiliser order by clause à la fin

 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) 

ou

tu peux faire comme ça

 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) 

Ajoutez simplement

 Order by max(OrderDate) 

à la fin.

 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) 

Maintenant, comment cela fonctionne:

Si vous commandz par mois, année séparément, il ira dans l'ordre croissant du mois dans l'ordre alphabétique (avril avant janvier). Si vous commandz par date de command, l'identifiant sera commandé en fonction d'une valeur de date qui est bien entendu sortingée par mois / année.

 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 

Essaye ça. Cela fonctionne parfaitement. Notez que vous entrez déjà une valeur d'année. Vous n'avez donc pas besoin de order ou de regrouper l'année puisque vous ne pouvez choisir qu'une seule année à la fois. Donc, le code supplémentaire n'est pas nécessaire.

 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) 

Utilisation La requête suivante fonctionnera pour vous.

 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**)