Vitalii Vitalii - 3 years ago 282
SQL Question

MSSQL: calculate user name lenght

In my

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

  • 200 users has name 1 char length

  • 500 users has name 2 chars length

  • 1500 users has name 3 chars length

  • ...

I created MSSQL query:

TOP 1000
LEN(nick) as 'title',
FROM [userstable]
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 Source

Group by is logically processed before Select so you cannot use alias there

SELECT TOP 1000 Len(nick) AS 'title',
FROM   [userstable] WITH(NOLOCK)
GROUP  BY Len(nick) 

Also read this article to know about NOLOCK Bad habits : Putting NOLOCK everywhere

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download