How to Categorize data and Find the Count for each Category in SQL Server

Categorize data and Find the Count for each Category in SQL Server

Suppose we have the two tables EMP, DEPT as below. Here our Objective is to Categorize the Employees as "IT" and "Non-IT" based on the DeptName, then we have to find Count of Employees in each group.

Select * From [dbo].[Tbl_Emp]

Select * From [dbo].[Tbl_Dept]

Now we have to Join the Emp table with Dept table , to get the DeptName.

Select E.*,D.DeptName From [dbo].[Tbl_Emp] E
Inner Join [dbo].[Tbl_Dept] D
On E.Dept_Id=D.DeptId

Order By DeptName

Now our Objective is to Categorize the Employees as "IT" and "Non-IT" based on the DeptName, the we have to find the Count of Employees in each group.

We can achieve this using the below sub-query.

Select ((Select Count (*) From [dbo].[Tbl_Emp])-S1.Cnt_NonIT) As Cnt_IT,S1.Cnt_NonIT
From (
   Select Count(Emp_Id) AS Cnt_NonIT From [dbo].[Tbl_Emp] E
   Inner Join [dbo].[Tbl_Dept] D
   On E.Dept_Id=D.DeptId and
   D.DeptName In ('BI&A','F&A')
  ) S1

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------