I have two tables:

EMPLOYEE: ID|DEPARTMENT_ID|CHIEF_ID|NAME|SALARY

DEPARTMENT: ID|NAME

The task here is to get list of departments with total max salary of all employees.

The query I'm trying to use (completes with no results):

SELECT s.DEPARTMENT_ID, s.SALARY_SUM
 
FROM (SELECT DEPARTMENT_ID, SUM(SALARY) SALARY_SUM, w.ID
 
      FROM EMPLOYEE e
 
      JOIN DEPARTMENT w ON  w.ID = e.DEPARTMENT_ID 

      GROUP BY e.DEPARTMENT_ID, w.ID) s

WHERE s.SALARY_SUM = (SELECT MAX(SALARY) MaxSum

                    FROM EMPLOYEE w1 

                    WHERE  w1.ID = s.ID)

CodePudding user response：

This will get you all the Deoatnent that have the highest Sum from their employees

CREATE tABLe EMPLOYEE(DEPARTMENT_ID INT, SALARY INT)
GO

CREATE TABLe DEPARTMENT(ID int)
GO

WITH CTE as (SELECT DEPARTMENT_ID, SUM(SALARY) SALARY_SUM, w.ID
 
      FROM EMPLOYEE e
 
      JOIN DEPARTMENT w ON  w.ID = e.DEPARTMENT_ID 

      GROUP BY e.DEPARTMENT_ID, w.ID)
     
SELECT s.DEPARTMENT_ID, s.SALARY_SUM
 
FROM CTE s

WHERE s.SALARY_SUM = (SELECT MAX(SALARY_SUM) MaxSum

                    FROM CTE w1 )
GO

DEPARTMENT_ID | SALARY_SUM
------------: | ---------:

db<>fiddle here

CodePudding user response：

You essentially have 4 data sets here.

1. EMPLOYEE e
2. DEPARTMENT w
3. Subquery1 s
4. Subquery2 (where clause)

Data set 3 has salary aggregated by Department ID. Data set 4 has the largest employee salary for each department.

The Where clause is saying compare 3 to 4 where SALARY_SUM = MaxSum. Because SALARY_SUM is aggregated by Department ID, and MaxSum is the largest individual salary, this where clause will only return results for departments with 1 person (or if people have a salary of $0).

If a department has multiple people with salaries, the SUM of the department's salary will always be greater than the largest individual salary in the department.