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?

Answer 1

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

Sign up