Date sorting issue in sql server

Question

Hello, I want to display date, according to last updated but my date sorting is wrong. I need to Result:- Months Request February 18 4 January 18 60 December 17 32 November 17 7 when i tried:- select FORMAT(CreatedDateTime,'MMMM yy') as Months, count(*) as Request from LRequests group by FORMAT(CreatedDateTime,'MMMM yy') order by FORMAT(CreatedDateTime,'MMMM yy') DESC so data displaying: Months Request November 17 7 January 18 60 February 18 4 December 17 32 then when i tried:- select FORMAT(CreatedDateTime,'MMMM yy') as Months, count(*) as Request from LRequests group by FORMAT(CreatedDateTime,'MMMM yy') order by CreatedDateTime DESC Getting error:- Column "LRequests.CreatedDateTime" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. Please help me. Thanks in Advance. Ankit Agarwal Software Engineer

Raja T · Answer

Hi, please check below code helpful for you.. CREATE TABLE [dbo].[Chit_Collection]( [ Receipt No ] [nvarchar](255) NULL , [ Date ] [datetime] NULL , [Customer ] [nvarchar](255) NULL , [GroupName ] [nvarchar](255) NULL , [Paid ] [nvarchar](255) NULL ) ON [ PRIMARY ] GO INSERT [dbo].[Chit_Collection] ([ Receipt No ], [ Date ], [Customer ], [GroupName ], [Paid ]) VALUES (N ' 1' , CAST (N '2017-11-25 00:00:00.000' AS DateTime), N 'Pavi ' , N '100 ' , N ' 100' ) INSERT [dbo].[Chit_Collection] ([ Receipt No ], [ Date ], [Customer ], [GroupName ], [Paid ]) VALUES (N ' 2' , CAST (N '2017-12-20 00:00:00.000' AS DateTime), N 'Sri ' , N '100 ' , N '100 ' ) INSERT [dbo].[Chit_Collection] ([ Receipt No ], [ Date ], [Customer ], [GroupName ], [Paid ]) VALUES (N ' 3' , CAST (N '2018-02-15 00:00:00.000' AS DateTime), N 'Mani ' , N '100 ' , N '100 ' ) INSERT [dbo].[Chit_Collection] ([ Receipt No ], [ Date ], [Customer ], [GroupName ], [Paid ]) VALUES (N ' 4' , CAST (N '2018-01-19 00:00:00.000' AS DateTime), N 'Pavi ' , N '200 ' , N '200 ' ) INSERT [dbo].[Chit_Collection] ([ Receipt No ], [ Date ], [Customer ], [GroupName ], [Paid ]) VALUES (N '5' , CAST (N '2017-11-01 00:00:00.000' AS DateTime), N 'test' , N '100' , N '65' ) INSERT [dbo].[Chit_Collection] ([ Receipt No ], [ Date ], [Customer ], [GroupName ], [Paid ]) VALUES (N '6' , CAST (N '2017-12-04 00:00:00.000' AS DateTime), N 'demo' , N '400' , N '500' ) INSERT [dbo].[Chit_Collection] ([ Receipt No ], [ Date ], [Customer ], [GroupName ], [Paid ]) VALUES (N '7' , CAST (N '2017-11-10 00:00:00.000' AS DateTime), N 'aaa' , N '650' , N '700' ) GO SELECT * FROM CHIT_COLLECTION GO SELECT { FN MONTHNAME([ DATE ]) } AS MONTHNAME, YEAR ([ DATE ]) AS YEAR , COUNT (*) AS REQUEST FROM CHIT_COLLECTION GROUP BY { FN MONTHNAME([ DATE ]) }, MONTH ([ DATE ]), YEAR ([ DATE ]) ORDER BY YEAR ([ DATE ]) DESC , MONTH ([ DATE ]) DESC GO

Raja T · Answer

Hi , Please refer below link.. https://stackoverflow.com/questions/17271316/order-by-month-and-year-in-sql-with-sum