CODEWITHSUNDEEP
sqloracle-database
Alpha2k
Alpha2k

Reputation: 2241

Oracle - how to calculate sum of highest salary for each dept

As far as i could get, getting the MAX sal of each dept: 

select ename, sal, deptno
from emp out
where sal = (
  select max(sal) from emp inside
  where out.deptno=inside.deptno
);

This returns 4 results, now I want the SUM of those 4 results, how do i do that? This seems not to work: 

select ename, sum(sal), deptno
from emp out
group by deptno, sal, ename
having sum(sal) = (
  select max(sal) from emp inside
  where out.deptno=inside.deptno
);

it is returning the same result as first query.

Upvotes: 0

Views: 2242

Answers (1)

juergen d
juergen d

Reputation: 204756

select sum(max_sal) as total_sum
from 
(
  select deptno, max(sal) as max_sal
  from emp
  group by deptno
) x

To get the highest salary for every department you can simply group by the department and use max() to get the highest. Use this as a subquery to sum these up.

Upvotes: 2

Related Questions