Consider following dataset:

I now want to find the manager who pays his subordinates the highest average salary plus bonus. A manager is someone who is present in of the mgr_id cells. So in this example Paul and Lucas are managers because their id is present in the mgr_id column of themselves and Max for Paul and Zack for Lucas. Basically I want MAX(AVG(salary bonus)) and then grouped by the mgr_id. How can I do that? I am using SQLite.

My expected output in this example would be simply the employee name 'Paul' because he has 2 subordinates (himself and Max) and pays them more than the other manager Lucas.

CodePudding user response：

SELECT 
       mrg_id
     , pay_avg
  FROM
     (
       SELECT mrg_id
            , AVG(salary   COALESCE(bonus,0)) pay_avg
         FROM <table>
        GROUP 
           BY mrg_id
     ) q
ORDER 
   BY pay_avg
      desc
LIMIT 1

CodePudding user response：

select top 1 t1.mgr_id,AVG((t1.salary) (t1.bonus)) as tot_sal
from #tbl_emps as t1
group by t1.mgr_id
order by AVG((t1.salary) (t1.bonus)) desc