Sunday, 17 December 2017

How to Calculate the Minimum, Maximum and Average Salary by Department in SQL Server

SQL Query to Calculate the Minimum, Maximum and Average Salary by Department
Scenario :
Suppose we have an Employee Table and its related Department Table as follows..
USE [EmpDB]
GO


SELECT [Emp_Id], [EmpName] ,[JobTitle] ,[Dept_Id]  ,
[EmpAge] ,[Experience] ,[NetSal]
FROM [dbo].[Tbl_Emp]
GO


SELECT [DeptId]
      ,[DeptName]
  FROM [dbo].[Tbl_Dept]
GO



Now we will Calculate the Minimum, Maximum and Average Salary by Department as follows :

1) Calculating Average of Salary drawing by the Employees whose JobTitle='Financial Analyst' from Emp_Table

Select JobTitle,Count(Emp_Id) CountOfEmp, Sum(NetSal) NetSal,
Round(AVG ( NetSal) ,2) AvgNetSal
     From Tbl_Emp
     Where JobTitle='Financial Analyst'
     Group By JobTitle

Result :

2) Calculating the Minimum, Maximum, Net Salary and Average Salary drawing by Department

Select D.DeptName, Count(E.Emp_Id) CountOfEmp,Sum(NetSal) NetSalDrawing,
    Round(Avg(E.NetSal),0) AvgNetSal, Min(E.NetSal) MinSal,Max(E.NetSal) MaxSal
    From Tbl_Emp E Inner Join Tbl_Dept D
 On E.Dept_Id=D.DeptId
 Group By D.DeptName

Result :


3) Calculating AVG of Salary drawing by the Employees whose Age Group between (>35 and <45)

Select D.DeptName,S1.CountOfEmp, S1.NetSal ,S1.AvgNetSal From
   (Select Dept_Id, Count(Emp_Id) CountOfEmp, Sum(NetSal) NetSal,
     Round(AVG ( NetSal) ,2) AvgNetSal
     From Tbl_Emp
     Where EmpAge Between 36 and 46
     Group By Dept_Id
   ) S1
 Inner Join Tbl_Dept D
 On S1.Dept_Id= D.DeptId

Result :


4) Calculating AVG of  Salary drawing by the Employees whose Age Group between
(>35 and <45) and Experience >=5

Select Dept_Id,D.DeptName,S1.CountOfEmp, S1.NetSal ,S1.AvgNetSal From
   (Select Dept_Id, Sum(NetSal) NetSal, Round(AVG ( NetSal) ,2) AvgNetSal ,
     Count(Emp_Id) CountOfEmp
     From Tbl_Emp
     Where (EmpAge Between 36 And  46) and [Experience]>=5
     Group By Dept_Id
   ) S1
 Inner Join Tbl_Dept D
 On S1.Dept_Id= D.DeptId

Result :


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts