Your sub-query returns two columns:
myDay
and
total
.
There is no column called
MyDate
to access in your outer query.
Try:
SELECT T.myDay, T.total
FROM
(
SELECT TOP(10) COUNT(*) AS 'total', day(myDate) AS 'myDay'
FROM tableName
GROUP BY Day(MyDate)
ORDER BY 'total'
) As T
However, in this instance, you don't need the outer query at all:
SELECT TOP(10) COUNT(*) AS 'total', day(myDate) AS 'myDay'
FROM tableName
GROUP BY Day(MyDate)
ORDER BY 'total'