Using AVG() function between two tables

Question

I have two tables, and I need to determine the company that offers the highest average salary for any position. My tables are as follows:

employer
eID (primary key), eName, location

position
eID (primary key), pName (primary key), salary)

The code I wrote determines all avg salaries that are higher than one, but I need to find only the highest average salary over all

Here is my code so far:

SQL> select eName
  2  from Employer E inner join position P on E.eID = P.eID
  3  where salary > (select avg(salary) from position);

This outputs all salaries that are higher than the lowest average, but I need only the highest average. I tried using avg(salary) > (select avg(salary) from position) but I received the error that group function is not allowed.

Any help or suggestions would be greatly appreciated!

Answer 1

Use:

SELECT x.eid, 
       x.ename, 
       x.avg_salary 
 FROM (SELECT e.eid,
              e.ename,
              AVG(p.salary) AS avg_salary,
              ROWNUM
         FROM EMPLOYER e
         JOIN POSTITION p ON p.eid = e.eid
     GROUP BY e.eid, e.ename
     ORDER BY avg_salary) x
 WHERE x.rownum = 1

Oracle 9i+:

SELECT x.eid, 
       x.ename, 
       x.avg_salary 
 FROM (SELECT e.eid,
              e.ename,
              AVG(p.salary) AS avg_salary,
              ROW_NUMBER() OVER(PARTITION BY e.eid
                                    ORDER BY AVG(p.salary) DESC) AS rank
         FROM EMPLOYER e
         JOIN POSTITION p ON p.eid = e.eid
     GROUP BY e.eid, e.ename) x
 WHERE x.rank = 1

Previously, because the question was tagged "mysql":

  SELECT e.eid,
         e.ename,
         AVG(p.salary) AS avg_salary
    FROM EMPLOYER e
    JOIN POSTITION p ON p.eid = e.eid
GROUP BY e.eid, e.ename
ORDER BY avg_salary
   LIMIT 1

Answer 2

select a.eid,
       a.ename,
       b.avg_salary
FROM EMPLOYER a
JOIN POSTITION b ON a.eid = b.eid
WHERE b.avg_salary =(SELECT max(x.avg_salary)
                      FROM (SELECT e.eid,
                                   e.ename,
                                   AVG(p.salary) AS avg_salary,
                            FROM EMPLOYER e
                            JOIN POSTITION p ON p.eid = e.eid
                            GROUP BY e.eid, e.ename) x
                    ) y