List the branch that monthly pays the most in salaries-CodePudding

I have this table, the expected output should be B003 since it's pays 54,000

STAFF	SALARY	BRAN
SL21	30000	B005
SG37	12000	B003
SG14	18000	B003
SA9	9000	B007
SG5	24000	B003
SL41	9000	B005

So far I only have this subquery, which isn't working how I expected.

SELECT BRANCHNO
FROM STAFF
WHERE (SALARY)  IN (SELECT MAX(SUM(SALARY))
                FROM STAFF
                GROUP BY BRANCHNO);

This works but I want a subquery that returns the branchno

SELECT MAX(SUM(SALARY))
FROM STAFF
GROUP BY BRANCHNO;

CodePudding user response：

select BRANCHNO max(sum_sal) 
from (SELECT BRANCHNO, SUM(SALARY) sum_sal
        FROM STAFF
    GROUP BY BRANCHNO) q1
group by BRANCHNO ;

The column used to group the rows can be displayed. So, add BRANCHNO to your select clause.

CodePudding user response：

One option is to use rank analytic function which ranks branches by sum of their salaries in descending order; you'd then return the one(s) that rank as the highest (rnk = 1).

Sample data:

SQL> with staff (staff, salary, bran) as
  2    (select 'SL21', 30000, 'B005' from dual union all
  3     select 'SG37', 12000, 'B003' from dual union all
  4     select 'SG14', 18000, 'B003' from dual union all
  5     select 'SA9' ,  9000, 'B007' from dual union all
  6     select 'SG5' , 24000, 'B003' from dual union all
  7     select 'SL41',  9000, 'B005' from dual
  8    )

Query:

  9  select bran
 10  from (select bran, rank() over (order by sum(salary) desc) rnk
 11        from staff
 12        group by bran
 13       )
 14  where rnk = 1;

BRAN
----
B003

SQL>