LEN(nick) as 'title',
GROUP BY title
column 'userstable.Nick' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Group by is logically processed before
Select so you cannot use alias there
SELECT TOP 1000 Len(nick) AS 'title', Count(*) FROM [userstable] WITH(NOLOCK) GROUP BY Len(nick)
Also read this article to know about
NOLOCK Bad habits : Putting NOLOCK everywhere