how to retrieve the employee who got maximum bonus?-CodePudding

I have to display the employee detail who has got the maximum bonus(one with employee details and another table with bonus details). Here I have created a 'performance bonus' column to sum up the multiple bonuses. How to retrieve the employee from that column?

select e.Employee_id,
       e.First_name,
       e.Department,
       e.Salary,
       coalesce((select sum(b.Bonus_Amount) 
    as Bonus-- Let's sum up all Employee's the bonuses
from Employee_Bonus_Table b
where b.Employee_ref_id = e.Employee_Id), 0) [Performance_bonus]
    from Employee_Table e

CodePudding user response：

If I understood the task correctly, then the bonuses can be repeated, is that right? then we must first sum up all the bonuses by employee, then sort from largest to smallest and get the first one from the list

--- for examples:
with Employee_Bonus_Table as(
    select Bonus_Amount = 1 ,Employee_ref_id = 1
    union select Bonus_Amount = 1000 ,Employee_ref_id = 2
    union select Bonus_Amount = 2000 ,Employee_ref_id = 2


),Employee_Table as (
    select Employee_id=1
       ,First_name='First_name'
       ,Department= 'Department'
       ,Salary = 1000

    UNION select Employee_id=2
       ,First_name='First_name2'
       ,Department= 'Department2'
       ,Salary = 2000
)
--reslut query:
select top 1
       e.Employee_id,
       e.First_name,
       e.Department,
       e.Salary,
       b.sumBonus_Amount
  from Employee_Table e
        join (select sumBonus_Amount = sum(Bonus_Amount), Employee_ref_id 
            from  Employee_Bonus_Table
            group by Employee_ref_id
            ) b on b.Employee_ref_id = e.Employee_Id
order by b.sumBonus_Amount desc