5

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Bill Gates
  • 87
  • 1
  • 2
  • 3

4 Answers4

7

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
Thomas
  • 63,911
  • 12
  • 95
  • 141
4

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)

davek
  • 22,499
  • 9
  • 75
  • 95
  • 1
    What if there is a tie for the highest avg? This will pick one of them at random. Is that what you want? – Todd Pierce Mar 14 '10 at 04:05
  • This made my day, is brilliant! Still I can't replicate it to MSSQL as it doesn't like the rownum. Based on this concept you can also use `select Top (1) * from (...)` which works like a charm in MSSQL (if cast over a table, populated be the AVG query) – Takedasama Nov 27 '13 at 14:09
  • Keep in mind that order by desc puts null values at the top, so it won't work if there's a null value in your wage column. – Boyen Jun 17 '15 at 15:41
  • @Boyen: the Oracle `AVG` function ignores values that are `NULL`, so the inner join will not return any null values in `ave_wage` – davek Jun 18 '15 at 07:18
2

Althogh the queries below show the same result as the other answers, it's nice to show users how it can be done as an alternative:

--Method 1 (Davek's select of 1st row over Order by) Brilliant!
--Method 2 (Thomas' where = sub-query result)
--Method 3 (Thomas' based on ranking)

--Method 4 (Inner join sub-queries)
select distinct a.department, a.wage from 
         (select distinct department, AVG(wage) as wage from employees group by department) as a
   inner join 
         (select Max(wage) as wage from
                (select distinct department, AVG(wage) as wage from employees group by department) as x) as b
   on a.wage = b.wage 
where a.wage = b.wage 

--Method 5 (AVG wage in (sub-query))
select distinct a.department, a.wage
from (select distinct department, AVG(wage) as wage from employees group by department) as a
Where a.wage in 
    (select Max(wage) as wage from
        (select distinct department, AVG(wage) as wage from employees group by department) as x)

Looking forward to seeing a custom function for this select also :)

Takedasama
  • 387
  • 1
  • 4
  • 16
0

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.

bryan
  • 1,031
  • 2
  • 17
  • 36