How to sort by date in Sql Server GROUP BY in date format?

Question

I have a table as follows:

Table: student_test_records Student TestDate Result ------- ------------------ ------- A 2015-01-26 17:28:11 Pass B 2015-01-24 17:28:11 Fail C 2015-01-26 17:28:11 Pass D 2015-01-26 17:28:11 Fail E 2014-05-23 17:28:11 Pass F 2013-06-23 17:28:11 Pass G 2014-05-23 17:28:11 Fail 

I am trying to write a query in SQL Server to display the number of past / failed results per day in the following format:

 FormattedDate Result Count ----------- ------ ----- May-23-2013 Pass 1 May-23-2014 Fail 1 May-23-2014 Pass 1 Jan-24-2015 Fail 1 Jan-26-2015 Fail 1 Jan-26-2015 Pass 2 

The following is the request:

 SELECT FORMAT(TestDate, 'MMM-dd-yyyy') as FormattedDate, Result, Count FROM student_test_records GROUP BY FORMAT(TestDate, 'MMM-dd-yyyy'), Result order BY FORMAT(TestDate, 'MMM-dd-yyyy'); 

The result set is correct, but the ordering is done using a formatted date string (month-day-year in alphabetical order). How to order the result for the actual date (TestDate)? In MySQL, I could do ORDER BY TestDate instead of the last line in the above query.

Answer 1

This should work:

 SELECT FORMAT(cast(TestDate as date), 'MMM-dd-yyyy') as FormattedDate, Result, Count(*) as Count FROM student_test_records GROUP BY cast(TestDate as date), Result ORDER BY cast(TestDate as date); 

Answer 2

Quite a lot already with him

 SELECT FORMAT(TestDate, 'MMM-dd-yyyy') as FormattedDate, Result, Count(*) FROM student_test_records GROUP BY TestDate, Result ORDER BY TestDate;