How can I find minimum among average salary of department table using groupby? (In SQL)-CodePudding

I am learning SQl and I have a question in mind as follow:

I want to find minimum among the avg(salaries) of department.

Schema: Department(dno, salary)

==================================================

what I tried is below query but I got error.

**select min(avg(salary)) from department groupby dno;**

Can anyone help me out with this, or any other possible solution?

CodePudding user response：

Using TOP 1:

select top 1 avg(salary) AS avg_salary
from department 
group by dno
order by avg_salary

CodePudding user response：

Using a select from subquery approach:

select min(T.avg_salary) as min_salary
from
(
    select avg(salary) as avg_salary
    from department
    group by dno
) T;

CodePudding user response：

conceptually, both min and avg are a set operation. pendantically, the min(avg(salary)) == avg(salary), because, there's only one avg for a given set of numbers. You could obviously, calculate

select min(salary), avg(salary) from department

This would tell you the smallest salary in the depeartment which would by definition be less than the avg unless all the salaries would be the same. Which is an odd use case.

You might be thinking of something like: select department, min(salary), max(salary), avg(salary) from employee group by department

To understand a new distrubution I will often use

select min(targetValue), max(targetValue), avg(targetvalue) from targetTable

To get a sense for the range in values.