MSSQL: calculate user name lenght [duplicate]

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?

Pரதீப் · Accepted Answer · 2017-10-02 14:31:48Z

4

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

answered Oct 2, 2017 at 14:31

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1