Query to Calculate Average of Employee Salaries Working Under Manager in SQL

Query to calculate Average of Employee Salaries working under manager in SQL

What we are basically doing here is self joining and grouping by the manager from E2 who tends to repeat for every manager_id entry. And we are retrieving the employee id and name for each entry in E2 after group by and we calculate the Average of salaries all the entries from E1

SELECT E2.EMP_ID, E2.EMP_NAME, AVG(E1.SALARY)
FROM MANAGER_EMP E1
INNER JOIN MANAGER_EMP E2
ON E1.MANAGER_ID = E2.EMP_ID
GROUP BY E2.EMP_ID, E2.EMP_NAME

The result set is

EMP_ID      EMP_NAME                                           EMP_AVG_SALARY
----------- -------------------------------------------------- ---------------
16          Rajesh                                             65000.000000
17          Raman                                              62500.000000
18          Santosh                                            53750.000000

(3 rows affected)

Query to select the manager and total salary of employees under the manager

The solution below assigns managers a salary of 0 as a start for the recursive sum of their employee salaries. This eliminates the need for a Hierarchy column, a second CTE cte_emp or a complex sum aggregation (Sum(Salary) Over (order by Hierchacy rows between 1 following and unbounded following))

Sample data

create table Employee
(
  EmpId int,
  Name nvarchar(3),
  Salary money,
  MgrId int
);

insert into Employee (EmpId, Name, Salary, MgrId) values
(1, 'CEO', 10000, NULL),
(2, 'M2',   8000, 1),
(3, 'M3',   6000, 2),
(4, 'M4',   6000, 2),
(5, 'M5',   5000, 4),
(6, 'M6',   1000, 2),
(7, 'M7',    500, 5);

Solution

with rcte as
(
  select e.Name as ManagerName,
         convert(money, 0) as Salary,
         e.EmpId as EmpId
  from Employee e
  where exists ( select 'x'
                 from Employee ee
                 where ee.MgrId = e.EmpId)
union all
  select r.ManagerName,
         e.Salary,
         e.EmpId
  from rcte r
  join Employee e
    on e.MgrId = r.EmpId
)
select r.ManagerName,
       sum(r.Salary) as TotalSalary
from rcte r
group by r.ManagerName;

Result

ManagerName  TotalSalary
-----------  -----------
CEO          26500.0000
M2           18500.0000
M4            5500.0000
M5             500.0000

Fiddle to see things in action with intermediate steps.

Avg Salaries under current managers

Here is the solution:

SELECT e2.emp_id AS "Manager_ID", e2.emp_name AS "Manager_Name", AVG(e1.salary) AS "AVG" 
FROM Employee e1,
            Employee e2
WHERE e1.manager_id = e2.emp_id
GROUP BY e2.emp_id, e2.emp_name 
ORDER BY e2.emp_id;

Description:

We want to join on the same table in this problem and we want to select the managers who are (5-6-7) in this case (e2) and the case is that e1 manager id is what we want, and to get the avg we use AVG function on the employees salaries (e1) in this case and we use GROUP BY to collect the avg and ORDER BY to order it.

SQL Query: "Write one SQL Query to calculate the maximum salaries for employees by Job Classification

Something like this:

select t.Job_Classification, max(e.salary) as 'Maximum_Salary_Class' 
from Employee e join Job_Title_Table_ t on e.JobID = t.JobId
group by t.Job_Classification;

Calculates the difference between employee salary and the average salary of job group

Depending on which database you are using, you may be able to arrive at this more efficiently using either window functions or CTEs. But a SQL solution that should work on pretty much any SQL-based database you could use would look like:

    SELECT a.emp_no,
           a.emp_fname, 
           a.emp_lname, 
           a.job_title, 
          (a.emp_salary - b.emp_salary) as salary_difference 
FROM employee AS a
INNER JOIN (
     SELECT job_title, avg(emp_salary)as emp_salary from employee group by    
             job_title) as b
    ON a.job_title = b.job_title

How to find sum of salary all of employees under each manager with out including manager salary in the sum?

if it means you don't want include salary of the person who is manger under his/her upper manager then :

select manager_id , sum(salary)
from employees
where emp_id not in (select manager_id from employee)
group by manager_id

if you must use join:

select manager_id , sum(salary)
from employees emp
left join employees mgr
 on mgr.emp_id = emp.managerid
where mgr.emp_id is not null
group by manager_id

---

Related Topics