In nested aggregate function using group by clause ex max(avg (salary )) first salary is calculated for each dept assuming grouping is done by dept and then max of that is selected . Now what I dint understand is that how multiple aggregate function works
select max(dept), max(avg(salary))
from employee
group by dept;
How will such a query work? Which aggregate function executes first? Can any one plz help me, I am new in SQL.
Let's say you have this table:
create table employee (dept number(4), salary number(12));
insert into employee values (10, 1000);
insert into employee values (10, 5000);
insert into employee values (20, 2500);
insert into employee values (20, 3500);
insert into employee values (30, 2000);
So average salaries in departments are:
select dept, avg(salary) avs from employee group by dept order by dept;
DEPT AVS
----- ----------
10 3000
20 3000
30 2000
Your query shows maximum value of department_id (30) and maximum value of average salary per department. These values are unrelated. In other words your SQL shows maximum values from previous output.
select max(dept), max(avg(salary)) from employee group by dept;
DEPT AVS
----- ----------
30 3000
If you want to show department(s) with highest value of average salary you have several options.
Everything depends on what you want to do if there is more than one such department, like in my example where two (10 and 20) have equal, highest average:
-- use HAVING clause with subquery (old school):
select dept, avg(salary) avs
from employee group by dept
having avg(salary) = (select max(avg(salary)) from employee group by dept)
order by dept;
-- use analytic MAX() function:
select dept, avs
from (
select dept, avg(salary) avs, max(avg(salary)) over () max_avs
from employee group by dept)
where avs = max_avs order by dept;
-- use analytic DENSE_RANK():
select dept, avs
from (
select dense_rank() over (order by avg(salary) desc) rnk, dept, avg(salary) avs
from employee group by dept)
where rnk = 1 order by dept;
-- if you want ONLY ONE ROW use MIN(...) or MAX(...) KEEP (DENSE_RANK...)
-- this returns department with lower ID:
select min(dept) keep (dense_rank last order by avg(salary)) dept,
min(avg(salary)) keep (dense_rank first order by avg(salary) desc) avs
from employee group by dept;
-- this returns department with higher ID:
select max(dept) keep (dense_rank last order by avg(salary)) dept,
max(avg(salary)) keep (dense_rank first order by avg(salary) desc) avs
from employee group by dept;
