CODEWITHSUNDEEP
sql
Kishan
Kishan

Reputation: 424

How to write an SQL query for this scenario?

I have a table containing attributes Id, Emp_name, dept_name, salary. Now i want to write an SQL query that will give me the dept_name value for which the overall salary of all employees belonging to that department is the highest, i.e dept for which sum of salaries of all its employees is the highest...? If there is any similar question with answer on stackoverflow, please suggest.. I dint find one. Thanks :)

I tried group by with sum() function, but i could not get how to find the maximum and compare it with sum.

Upvotes: 1

Views: 1632

Answers (5)

user1078445
user1078445

Reputation: 66

I don't know exactly know your requirements, but perhaps there is another point to be considered: Two (or more) departments could have the same sum of salary.

I have not tested the query, but this should give you all departments which have the maximum some of salary:

select dept_name FROM table_name GROUP BY dept_name HAVING SUM(salary)=(select MAX(sum_salary) FROM (select SUM(salary) AS sum_salary FROM table_name GROUP BY dept_name))

Upvotes: 0

dfsq
dfsq

Reputation: 193261

SELECT dept_name FROM table
GROUP BY dept_name
ORDER BY SUM(salary) DESC
LIMIT 1

And you would also better have you departments in another table linked to the first table via foregn keys. Just a note.

Upvotes: 0

Mauro Gagna
Mauro Gagna

Reputation: 173

try this:

select top 1 dept_name from myTable group by dept_name order by sum(salary) desc

Upvotes: 0

Bort
Bort

Reputation: 7618

Can you do

    SELECT TOP 1 dept_name FROM table GROUP BY dept_name ORDER BY SUM(salary) DESC

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 30765

Seems like a textbook example for GROUP BY:

select dept_name, total_salary from (
  Select dept_name, sum(salary) as total_salary
  from my_table
  group by dept_name
) order by total_salary desc

Upvotes: 0

Related Questions