Date sorting issue in sql server

Ankit Agarwal

8y927

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Raja T8y ago
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 T8y ago
Hi , Please refer below link.. https://stackoverflow.com/questions/17271316/order-by-month-and-year-in-sql-with-sum