SQL - Getting MAX AVG

user_8972467 0
Viewed 3118

So I have been trying to solve this for a while, and even though I have found many interesting things here I simply could not solve it the way it has been requested.

I have two tables:

PROFESSOR (ID, NAME, DEPARTMENT_ID and SALARY) and
DEPARTMENT (ID, NAME).

I have to write a query that shows the DEPARTMENT NAME that has the HIGHEST average SALARY. Also if more than one department have the highest average SALARY, should list all of then in any order.

I have tried so many things and in the end I created a monster, I think. i tried using HAVING but it did not work the way I did. I'm lost. The problem is that I need to use to aggregate functions.

SELECT b.nam, b.average
FROM ( SELECT DEPARTMENT.NAME AS nam, AVG(PROFESSOR.SALARY) AS average
   FROM PROFESSOR JOIN DEPARTMENT ON (PROFESSOR.DEPARTMENT_ID = 
DEPARTMENT.ID)
   GROUP BY DEPARTMENT.NAME) AS b
GROUP BY b.nam, b.average
ORDER BY b.average DESC

But this query is bringing me all the departments with the average, not the maximum.

If someone could please assist me and explain in a easy way I would really appreciate it. Thanks!

sql-server sql
4 Answers

You can use this. If more than one row has same max avg value, with using WITH TIES you can bring all of them.

SELECT TOP 1 WITH TIES DEPARTMENT.NAME AS nam, AVG(PROFESSOR.SALARY) AS average
FROM PROFESSOR 
JOIN DEPARTMENT ON (PROFESSOR.DEPARTMENT_ID = DEPARTMENT.ID)
GROUP BY DEPARTMENT.NAME
ORDER BY AVG(PROFESSOR.SALARY) DESC
user_85001100 
;WITH x AS (

        SELECT t.dept,
               T.avg_sal,
               rank() OVER(ORDER BY t.avg_sal DESC) AS rnk

        FROM
           (
                SELECT d.name AS 'dept',
                       avg(p.salary) AS avg_sal
                FROM department AS d
                     INNER JOIN
                     professor AS p ON p.department_id=d.id
                GROUP BY d.name
            ) AS t
)

-- all depts with highest avg sal
SELECT dept, avg_sal
FROM x
WHERE rnk = 1
user_37754310

You can subquery for the MAX(avgSalary). The way I've done it here was to use a CTE.

WITH cte AS
(
SELECT DEPARTMENT_ID
      ,AVG(SALARY) [avgSalary]
  FROM PROFESSOR
GROUP BY DEPARTMENT_ID
)

SELECT D.[NAME]
      ,cte.avgSalary
  FROM cte INNER JOIN DEPARTMENT D
            ON D.ID = cte.DEPARTMENT_ID    
 WHERE cte.avgSalary = (SELECT MAX(avgSalary)
                          FROM cte)
user_30643450

I think what you want is:

select
  NAME,
  max(avg_salary) as max_avg_salary
from
  DEPARTMENT d inner join
  (select
    DEPARTMENT_ID ,
    avg(SALARY) as avg_salary
  from
    PROFESSOR 
  group by
    DEPARTMENT_ID) a on
  d.DEPARTMENT_ID = a.DEPARTMENT_ID

there are other ways to do it as you see in the other answers, but I think you want the simplest solution possible using group by to determine both each avg and the max of all avgs. Only other thing you need is a subquery, which you're probably familiar with.

HTH

user_1375080
Related