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