SQL Server Group by Alias Column Name with Example

While working with some query I got requirement like implement group by for alias column in sql server. Generally, it’s not possible for us to use alias column names in query functionality like where or group by or order by, etc. To implement group by for alias column we need to wrap all sql query statements in subquery and implement grouping on outside of the statement.

To implement group by to alias column name in sql server we need to write the query like as shown below

Select Joindate,count(Id) as NumberofEmployees FROM ( Select Id,Name,CreatedDate as Joindate from @temp ) subdata group by Joindate

If you observe above query “JoinDate” is an alias column name in sub query and we are using same “JoinDate” alias column in group by outside of that statements in sql server.

Now we will see complete example of implementing group by to alias column name in sql server with example.

INSERT INTO @temp(Id,Name, CreatedDate) Values(1,'Suresh Dasari',getdate()), (2,'Rohini Alavala',getdate()+2), (3,'Madhav Sai',getdate()-30), (4,'Praveen Alavala',getdate()+2), (5,'Mahendra Dasari',getdate()-30) Select Joindate,count(Id) as NumberofEmployees FROM ( Select Id,Name,CreatedDate as Joindate from @temp ) subdata group by Joindate

If you observe above query we insert data in temp table and applying group by on alias column based on our requirement. Now we will and see the output that would be like as shown below.

I hope it helps you to get apply group by on alias column name in sql server.