0

I am trying to write a query which gives the highest earner (Salary+Bonus) and name of the employee and ID for each department.

I have the following table named Tab1:

ID Emp Dep Salary Bonus
1 Mary X 50
2 Adam Z 60 15
3 Brad X 55 20
4 Fred Y 80 30
5 Irene Y 90
6 Alan Z 100 10

I've tried the following query:

SELECT Dep, MAX(Salary) 
FROM Tab1
GROUP BY Dep

But this just give me the salary without bonus. I also want to get ID and Emp as a query result.

So the expected result would be:

ID Emp Dep Max_total_salary
3 Brad X 75
4 Fred Y 110
6 Alan Z 110

I would appreciate any suggestion you have!

Zhorov
  • 28,486
  • 6
  • 27
  • 52
complog
  • 67
  • 7
  • you mean the salary and bonus summed up together ? – GuidoG Apr 13 '22 at 13:09
  • You [asked this](https://stackoverflow.com/questions/71856833/sql-query-to-find-the-highest-earner-for-each-department-with-their-name) earlier, the answer is the same. If you don't understand the linked duplicates then you explain in your question what about them you don't understand. If you think your question isn't a duplicate, then [edit](https://stackoverflow.com/posts/71856833/edit) your **original** question to explain why it isn't a duplicate; don't repost it. – Thom A Apr 13 '22 at 13:09
  • I checked the links, but still couldn't find any case would fit my situation. – complog Apr 13 '22 at 13:11
  • The answer is still the same, @squillman . `MAX` isn't what they want/need. – Thom A Apr 13 '22 at 13:12
  • @larnu Yeah, sorry. Skimmed over that the other was a dupe.... Saw the error and figured it was deleted because the syntax was fixed here. – squillman Apr 13 '22 at 13:12
  • I didn't mean to duplicate it. I tried to ask my question with more clear explanation. The reason it's not the same with the link is, I need to sum two columns up and then use that column in the query – complog Apr 13 '22 at 13:17
  • The logic is *identical* @complog , you are just ordering by `Salary` plus `Bonus`, instead of just `Salary`. I would *assume* you know how to use the plus operator. – Thom A Apr 13 '22 at 13:27

0 Answers0