Search code examples
sqloracle-sqldeveloperanalytic-functions

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

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

Solution

  • 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.