Thursday, December 13, 2018

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog