What is the average salary of the employees who have the highest commission

Question

I am trying to do this question and need some hints, I want to do it with the analytic function in oracle sql. What is the average salary of the employees who have the highest commission

Answer 1

it is hard to give you an proper answer without any details, but I will try. You can do something like this:

SELECT
  T1.EMPLOYEE_NAME AS NAME,
  T1.SALARY AS SALARY,
  AVG(CASE WHEN T1.COMMISSION >=(
       SELECT MAX(COMMISSION) FROM EMPLOYEETABLE)
           THEN SALARY
           ELSE 0
           END) AS AVG_SALARY
FROM EMPLOYEETABLE T1
GROUP BY T1.EMPLOYYEE_NAME, T1.SALARY
ORDER BY T1.SALARY DESC 

Maybe you can give further details on your table structure and data model. Bye.