how can I solve error 207, level 1 invalid column domain-CodePudding

I am facing a problem with my query. My group by statement in my table has "domain" I get error :

error 207, level 16, state 1, invalid column name "domain".

If I do it without domain, then it is no problem.

select email, stuff(email, 1, charindex('@', email) , '') as domain
FROM [dbo].[Testdatabase]
group by email,domain

The idea behind this is to have a domain name of each email.

CodePudding user response：

Could you try this one :

select email, stuff(email, 1, charindex('@', email) , '') as domain

FROM [dbo].[Testdatabase]

group by email,stuff(email, 1, charindex('@', email) , '')

CodePudding user response：

I hope you are little bit aware of order of execution in SQL. SELECT clause will get executed after GROUP BY clause. So, alias would not have applied yet. Write the query as given below:

select email, stuff(email, 1, charindex('@', email) , '') as domain
FROM [dbo].[Testdatabase]
group by email, stuff(email, 1, charindex('@', email) , '')

CodePudding user response：

Thank you @Beso for your quick help. i also find another solution, it is like this:

select email, domain FROM [dbo].[Testdatabase] cross apply ( select stuff(email, 1, charindex('@', email) , '') as domain) alias group by email,domain