Maximum of averages

Question

I'm supposed to get every departments average wage and only show the department with the highest average wage. I figured out this query, but it doesn't work. Anyone got some ideas?

SELECT department, max(avg(wage))
FROM employees
GROUP BY department;

I get this error: ERROR at line 1: ORA-00937: not a single-group group function

Answer 1

By Googling...

Cause: A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.

Action: Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed.

Answer 2

does this work:

select *
from
(
  SELECT 
      department
      , avg(wage) as ave_wage
  FROM employees 
  GROUP BY department
)x 
order by ave_wage desc 
where rownum < 2;

(disclaimer: completely untested, so I may have put the rownum bit in the wrong place)

Answer 3

Without CTEs you can do:

Select Z.Department, Z.AvgWage
From  (
        Select Department, Avg(Wage) AvgWage
        From Employees
        Group By Department
        ) As Z
Where AvgWage = (
                            Select Max(Z1.AvgWage)
                            From (
                                    Select Department, Avg(Wage) AvgWage
                                    From Employees
                                    Group By Department
                                    )  Z1
                            )

With CTEs you could do:

With AvgWages As
    (
    Select Department
        , Avg(Wage) AvgWage
        , Rank() Over( Order By Avg(Wage) Desc ) WageRank
    From Employees
    Group By Department
    )
Select Department, AvgWage, WageRank
From AvgWages
Where WageRank = 1