Using AVG() function between two tables
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 开发者_StackOverflow中文版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!
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
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
精彩评论