Re: Simple SQL question...

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 17 May 1999 13:55:01 GMT
Message-ID: <37401a93.2715044@inet16.us.oracle.com>

On Mon, 03 May 1999 17:23:54 GMT, notronrj_at_my-dejanews.com wrote:

>Hello,
>
>Got a SQL question. I think it's a simple one, but I am fairly new
>at SQL. I just took the Intro to SQL from Oracle Education.
>
>I have a table "EMP" that contains employess, dept number and salary and
>other info.
>
>I want to write a query that returns the dept with the highest combined
>salary.
>
>So I have done:
>
>select max(sum(sal)) from emp
>group by deptno;
>
>And this works great. But I would like to be able to also output the deptno
>this value is from.
>
>So I tried,
>
>select deptno, max(sum(sal)) from emp
>group by deptno;
>
>And I get an error saying that select deptno...
>is not a group function or something to that effect.
>
>
>the error is:
>
>ERROR at line 1:
>ORA-00937: not a single-group group function
>

Try this...

If this is your data...

SQL> select sum(sal), deptno
  2 from emp
  3 group by deptno
  4 /

SUM(SAL) DEPTNO
---------- ----------

      8750         10
     10875         20
      9400         30

then ...

  1 select sum(sal), deptno
  2 from emp
  3 having sum(sal) >= all ( select sum(sal) from emp group by deptno )   4* group by deptno
SQL> /   SUM(SAL) DEPTNO
---------- ----------

10875 20

  1 select sum(sal), deptno
  2 from emp
  3 having sum(sal) = ( select max(sum(sal)) from emp group by deptno )   4* group by deptno
SQL> /   SUM(SAL) DEPTNO
---------- ----------

10875 20

  1 select sum(sal), deptno
  2 from emp, (select max(sum(sal)) sal_max from emp group by deptno )   3 having sum(sal) = sal_max
  4* group by deptno, sal_max
SQL> / will work.

SUM(SAL) DEPTNO
---------- ----------

10875 20

Or in Oracle8i you could do

1 select *
2 from ( select deptno, sum(sal) sal

  3             from emp
  4            group by deptno
  5            order by 2 desc )

6* where rownum = 1
SQL> / DEPTNO SAL
---------- ----------

20 10875

hope this helps.

chris.

>
>
>How the heck do I do this?
>
>Thanks for the help,
>
>Newbie
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon May 17 1999 - 08:55:01 CDT