CODEWITHSUNDEEP
sqlsql-server-2008
DavyJonesUA
DavyJonesUA

Reputation: 2319

SQL query: how can I get "id" of department in which employees receive the maximum salary?

How can I get id of department in which employees receive the maximum salary:

Employee table: Empl (ID, FirstName, LastName, Salary, DeptId)
Departments table: Dept (ID, City)

rus (Вывести “id” подразделения, в котором сотрудники получают максимальную заработную плату.)

Upvotes: 1

Views: 1671

Answers (6)

sianabanana
sianabanana

Reputation: 910

SELECT  DepartmentId
FROM    Employee
WHERE   Salary = (SELECT MAX(Salary) FROM Employee)

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

EDIT: Changed SUM(Salary) to AVG(Salary) based on comments on the question.

SELECT TOP 1 DeptId
    FROM Employees
    GROUP BY DeptId
    ORDER BY AVG(Salary) DESC

Upvotes: 2

Ben
Ben

Reputation: 52853

select id
  from dept
 where id = ( select deptid
                from ( select max(avg_salary), deptid
                         from ( select deptid, avg(salary) as avg_salary
                                  from empl
                                 group by deptid )
                        group by deptid )
                     )

:-)

Upvotes: 0

Contristo
Contristo

Reputation: 335

I would assume you mean max average salary of a department and not the single highest salary across all departments.

However it seems all you would have to do is use the following SQL functions MAX function AVG function

group by department ID and viola.

Thought I agree with the comments above, I will assume you are doing this for research ;-)

Upvotes: 0

Lamak
Lamak

Reputation: 70638

SELECT TOP 1 B.*
FROM (SELECT DeptId, AVG(Salary) AvgSalary
      FROM Empl
      GROUP BY DeptId) A
INNER JOIN Dept B
ON A.DeptId = B.Id
ORDER BY AvgSalary DESC

Upvotes: 1

marc_s
marc_s

Reputation: 754388

To get the one single Department's ID where the highest single salary is paid:

SELECT TOP 1 DeptID
FROM dbo.Empl
ORDER BY Salary DESC

Or are you looking for something else?

Upvotes: 0

Related Questions