SQL Server Group by Alias Column Name with Example

By: Suresh Dasari May 30, 2016

Introduction:

Here I will explain how to group by alias column name in sql server or alias column name in sql server or how to use group by on an alias column in sql server. To implement group by on alias column name in sql server we need to use the whole expression in group by clause or we need to wrap all sql query statements in subquery and implement grouping on outside of the statement.

Description:

In previous articles I explained sql server get next and previous row values from sequence, sql server get before and after character example, sql server keyboard shortcut keys, sql server show exact match on top then partial match values, SQL Server single procedure to insert update delete, Primary key constraint in sql server, foreign key constraint in sql server and many articles relating to SQL server. Now I will explain how to group by alias column name in sql server.

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.