OraFAQ Forum: Server Utilities » Problem with Group by

Home » RDBMS Server » Server Utilities » Problem with Group by

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with Group by [message #71355]

Tue, 22 October 2002 05:26

Sam
Messages: 255
Registered: April 2000

Senior Member

From the scott/tiger emp table, i want to find out which department pays the total highest salary. i dont seem to get the right SQL. i tried,
select deptno, max(sum(sal)) from emp group by deptno
but it doesn't work. can anyone suggest, please.
thanks, SAM

Report message to a moderator

Re: Problem with Group by [message #71359 is a reply to message #71355]

Tue, 22 October 2002 07:23

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> ed
Wrote file afiedt.buf

  1  select deptno,a from
  2  (
  3     select deptno,sum(sal) a from emp
  4     group by deptno
  5     order by a desc
  6  )
  7* where rownum < 2
SQL> /

    DEPTNO          A
---------- ----------
        30       9400

Report message to a moderator

Re: Problem with Group by [message #71361 is a reply to message #71355]

Tue, 22 October 2002 07:43

B
Messages: 327
Registered: August 1999

Senior Member

not the best way but ...

select deptno, total from ( select deptno,sum(sal) total from emp group by deptno )
where total = ( select max(total ) from ( select deptno,sum(sal) total from emp group by deptno ))

or

1 select deptno, sum(sal) total from emp
2 group by deptno
3 having sum(sal) = ( select max(subtotal) from ( select sum(sal) subtotal from emp group by deptno ))

may be some better ways....

Report message to a moderator