I have an instructor(ID,name,dept_name,salary) schema
and need to run a query to " Find the maximum salary across all departments of the total salaries of each department"

The queries (i) and (ii) below give me the same (correct) result, but the query (iii) returns no result. Could you please let me know why this happens? Similar nested set comparisons subqueries (with set comparisons that use >=all without a group by; >= some with a group by) give correct results.

i)select max(sum(salary)) from instructor group by dept_name

ii)select max(tot_sal) from (select dept_name, sum(salary) as tot_sal from instructor
group by dept_name);

iii)select dept_name,sum(salary) from instructor
group by dept_name having sum(salary) >= all
(select sum(salary) from instructor group by dept_name);



The data in my table is as under
10101 Srinivasan Comp. Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein Physics 95000
32343 El Said History 60000
33456 Gold Physics 87000
45565 Katz Comp. Sci. 75000
58583 Califieri History 62000
76543 Singh Finance 80000
76766 Crick Biology 72000
83821 Brandt Comp. Sci. 92000
98345 Kim Elec. Eng. 80000
1111 abc ADEPT (null)

create table t (
  ID int,name varchar2(20),dept_name varchar2(20),salary int
);

insert into t values (10101,'Srinivasan','Comp. Sci.',65000);
insert into t values (12121,'Wu','Finance',90000);
insert into t values (15151,'Mozart','Music',40000);
insert into t values (22222,'Einstein','Physics',95000);
insert into t values (32343,'El Said','History',60000);
insert into t values (33456,'Gold','Physics',87000);
insert into t values (45565,'Katz','Comp. Sci.',75000);
insert into t values (58583,'Califieri','History',62000);
insert into t values (76543,'Singh','Finance',80000);
insert into t values (76766,'Crick','Biology',72000);
insert into t values (83821,'Brandt','Comp. Sci.',92000);
insert into t values (98345,'Kim','Elec. Eng.',80000);
insert into t values (1111,'abc','ADEPT',null);

select dept_name,sum(salary) from t
group by dept_name having sum(salary) >= all
(select sum(salary) from t 
where salary is not null
group by dept_name);

DEPT_NAME   SUM(SALARY)  
Comp. Sci.  232,000 

select dept_name,sum(salary) from t
group by dept_name having sum(salary) >= all
(select nvl(sum(salary), 0) from t 
group by dept_name);
     
DEPT_NAME   SUM(SALARY)  
Comp. Sci.  232,000