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

i'm using SQL server not (MYSQL) and 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) as  averagr_salary
from
(
select  d.Dname,avg(e.salary) as averagr_salary
from Employee e
inner join  Departments d
on  d.Dnum = e.dno
group by e.Dno,d.Dname
) as tbl
group by tbl.Dname
order by averagr_salary ASC limit 1;

Demo: https://www.db-fiddle.com/f/vhqJXYFy52xRtVBc97R1EL/1

For SQL Server:

Select top 1 tbl.Dname,min(averagr_salary) as  averagr_salary
from
(
select  d.Dname,avg(e.salary) as averagr_salary
from Employee e
inner join  Departments d
on  d.Dnum = e.dno
group by e.Dno,d.Dname
) as tbl
group by tbl.Dname
order by averagr_salary ASC;

Demo:https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=303b9fab3403397b1df8ee79b9b216a6

CodePudding user response：

Fiddle (Updated): https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=bb4d5b2989e06f92bcb8c39742798ba4

Some stackoverflow UI controls are acting strangely. Many of the normal controls, commenting, edit controls, etc, have not been functional for about 12 hours.

This solution is standard SQL and will work with SQL Server (recent versions):

Also notice, the logic works when there are multiple groups with an average which matches the minimum, not just one.

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
;