how to retrieve the minimum of multi average like this min(avg(e.salary)) by Aggregate function sql-CodePudding

i'm trying to retrieve the minimum of multi averages of salaries in or group by their departments.

i can retrieve the salary's averages of all departments by (group by) by using this query:

select  d.Dname,avg(e.salary) as averagr_salary
from Employee e, Departments d
where d.Dnum = e.dno
group by e.Dno,d.Dname

the results of data like that (the Department_Name and the average_salary):

name avg
DP1 139
DP2 1050
DP3 1250

i want to retrieve only the minimum of these multi averages like that:

name avg
DP1 139

i tried this query but it shows error:

select  d.Dname,min(avg(e.salary)) as averagr_salary
from Employee e, Departments d
where d.Dnum = e.dno
group by e.Dno,d.Dname

the error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

could you help me

CodePudding user response：

It should be something like:

Select tbl.Dname,min(averagr_salary) 
from
(
select  d.Dname,avg(e.salary) as averagr_salary
from Employee e, Departments d
where d.Dnum = e.dno
group by e.Dno,d.Dname
) as tbl
group by tbl.Dname;

CodePudding user response：

Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9720f4d89bb1683afd089a98b37a0324

Here's one approach:

SELECT d.Dname
     , AVG(e.salary) AS average_salary
  FROM Employee    e
  JOIN Departments d
    ON d.Dnum = e.Dno
 GROUP BY e.Dno, d.Dname
HAVING average_salary = (
        SELECT AVG(e.salary) AS av
          FROM Employee e
         GROUP BY e.Dno
         ORDER BY av ASC
         LIMIT 1
   )
;

and if you have MySQL 8.0 :

WITH cte AS (
        SELECT d.Dname
             , AVG(e.salary) AS average_salary
             , RANK() OVER (ORDER BY AVG(e.salary)) AS n
          FROM Employee    e
          JOIN Departments d
            ON d.Dnum = e.Dno
         GROUP BY e.Dno, d.Dname
     )
SELECT Dname, average_salary
  FROM cte
 WHERE n = 1
;