Calculating Employees with Salaries Above Average Departmental Salary

In this blog post, we’ll explore two methods for calculating the number of employees in each department who have salaries above the average salary of their respective departments. We’ll start by examining the SQL query provided in the Stack Overflow question and then delve into alternative approaches using sub-queries and conditional aggregation.

Understanding the Problem Statement

The problem statement is asking us to write a SQL query that returns three columns: Dname, No_emp, and Mgr_dept. The first column, Dname, represents the department name. The second column, No_emp, counts the number of employees in each department who have salaries above the average salary of their respective departments. The third column, Mgr_dept, displays the name of the manager in each department (if available).

Examining the Provided SQL Query

The original SQL query provided attempts to solve this problem using basic table joins and sub-queries. However, it has some limitations and inaccuracies.

Limitations of the Original Query

1. The first part of the query (where e.sal > (select avg(sal) from emp where m.deptno=e.deptno group by m.deptno) ) only considers employees with salaries above the department average, but it does not account for managers.
2. The second part of the query (group by e.deptno,d.dname) is incorrect as it should be d.dname instead of both e.deptno and d.dname.

Corrected SQL Query

Let’s rewrite the original query with some corrections to accurately represent our solution.

-- Query 1: Joining to a sub-query for employees above average salary
SELECT 
    d.dname as department,
    e.ename as manager,
    depstat.above_average_salary
FROM DEPT d
LEFT JOIN EMP e ON e.deptno = d.deptno AND e.job = 'MANAGER'
JOIN (
    SELECT deptno, COUNT(empno) as above_average_salary
    FROM EMP e1
    WHERE sal > (SELECT AVG(sal) 
                 FROM EMP e2 
                 WHERE e2.deptno = e1.deptno)
    GROUP BY deptno
) depstat ON depstat.deptno = d.deptno
ORDER BY d.dname;

Method 1: Joining to a Sub-Query for Employees Above Average Salary

The corrected query above uses a sub-query to calculate the number of employees with salaries above the average departmental salary. The main query then joins this sub-query with the DEPT and EMP tables.

Here’s how it works:

• It first selects all departments (d) from the DEPT table.
• Then, for each department, it left-joins the EMP table to match employees with their respective departments.
• The sub-query calculates the number of employees in each department who have salaries above the average salary. This is done by comparing the employee’s salary to the average salary within the same department (e1.deptno = e2.deptno). The count is then grouped by deptno for further processing.
• Finally, it orders the results by department name.

This approach ensures that all managers are included in the output, even if there are no employees with salaries above average in their respective departments.

Expected Results

For our sample database schema:

The corrected query will produce the following output:

Method 2: Using Conditional Aggregation

Another approach to solve this problem is by using conditional aggregation. This method eliminates the need for a sub-query and directly calculates the required values on the fly.

-- Query 2: Using conditional aggregation for employees above average salary
SELECT 
    d.dname as department,
    MAX(CASE WHEN emp.job = 'MANAGER' THEN emp.ename END) as manager,
    COUNT(CASE WHEN emp.sal > depstat.avg_sal THEN emp.empno END) as above_average_salary
FROM DEPT d
LEFT JOIN EMP emp ON emp.deptno = d.deptno
JOIN (
    SELECT deptno, AVG(sal) as avg_sal
    FROM EMP
    GROUP BY deptno
) depstat ON depstat.deptno = d.deptno
GROUP BY d.deptno, d.dname
ORDER BY d.dname;

This query works similarly to the first approach but uses conditional aggregation instead of a sub-query.

Here’s how it works:

• It first selects all departments (d) from the DEPT table.
• Then, for each department, it left-joins the EMP table to match employees with their respective departments.
• The main query then joins this with another sub-query that calculates the average salary for each department (depstat). This is done by grouping emp by deptno.
• It uses two conditional aggregation functions: MAX(CASE WHEN emp.job = 'MANAGER' THEN emp.ename END) to display the manager’s name, and COUNT(CASE WHEN emp.sal > depstat.avg_sal THEN emp.empno END) to count employees with salaries above average.
• Finally, it orders the results by department name.

This approach also ensures that all managers are included in the output, just like the first method.

Expected Results

The query’s expected results match those of the corrected sub-query query:

Comparing Method 1 and Method 2

The two methods presented above, which use a sub-query for employees above average salary and conditional aggregation respectively, share the common goal of calculating employees’ salaries in each department. While both approaches provide accurate results, they also have distinct characteristics.

Differences Between Method 1 and Method 2:

• Approach to Calculating Above Average Salary: The first method uses a sub-query to calculate above-average salary values for each department. In contrast, the second approach employs conditional aggregation functions directly in the query.
• Handling Missing Manager Information: Both methods correctly handle missing manager information by utilizing LEFT JOINs or NULL checks. However, the first method may produce incorrect results if there are no employees with salaries above average in a given department, whereas the second approach will always yield accurate results for all departments.

Conclusion

In this blog post, we explored two approaches to solving the problem of finding the number of employees who have salaries above the average salary within each department. We examined both sub-query and conditional aggregation methods to accurately calculate these values while handling missing manager information.

The corrected SQL queries provided earlier can be applied directly in your database management system of choice to obtain accurate results for your specific use case.

Last modified on 2024-08-20