MSSQL: calculate user name lenght

Question

In my users table I need to calculate count of users by username length. For example:

I created MSSQL query:

SELECT 
  TOP 1000
  LEN(nick) as 'title',
  Count(*)
FROM [userstable]
WITH(NOLOCK)
GROUP BY title

After I run it I see next problem:

column 'userstable.Nick' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Could someone explain why?

You can't use the alias name in the group by – S3S Oct 02 '17 at 14:34 — S3S, Oct 02 '17 at 14:34

score 4 · Accepted Answer · answered Oct 02 '17 at 14:31

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

1 Answers1