1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help Needed in Query

Discussion in 'SQL PL/SQL' started by kalir, Mar 11, 2011.

  1. kalir

    kalir Guest

    hey guys, i need some help... i am student and learning oracle 10g
    i need lil bit help, i cant solve the query... :(
    the query is
    "Show deptno with lowest sal having high average salary"

    plzz any one can helppp mee....

    thanx in advance :)
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    What's the query you have tried to write?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post the exact problem you're trying to solve as what you did post isn't very clear.
     
  4. vldpriya

    vldpriya Active Member

    Messages:
    5
    Likes Received:
    1
    Trophy Points:
    65
    Hi Kalir,

    Try this

    SELECT Deptno, MIN(Salary) 'Lowest' from emp
    where deptno in (SELECT deptno FROM (select deptno, MAX(AVG(salary)) FROM emp GROUP BY deptno ))
    group by deptno

    Thanks and Regards,
    Dhanapriya V L
     
    kalir likes this.
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    @ vldpriya;

    Your solution gives error.

    Code (SQL):
    SQL> SELECT Deptno, MIN(Sal) "Lowest" FROM emp
      2  WHERE deptno IN (SELECT deptno FROM (SELECT deptno, MAX(AVG(sal)) FROM emp
    GROUP BY deptno ))
      3  GROUP BY deptno;
    WHERE deptno IN (SELECT deptno FROM (SELECT deptno, MAX(AVG(sal)) FROM emp GROUP
     BY deptno ))
                                                *
    ERROR at line 2:
    ORA-00937: NOT a single-GROUP GROUP FUNCTION


    SQL> SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno ;
    SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno
           *
    ERROR at line 1:
    ORA-00937: NOT a single-GROUP GROUP FUNCTION
    @ Kalir,

    You might be expecting a query as shown below, though it is not very clear what REALLY you are trying to do.

    Code (SQL):
    SQL> SELECT DEPTNO,AVGSAL,MINSAL
      2  FROM (
      3  SELECT DEPTNO,AVG(SAL) AVGSAL,MIN(SAL) MINSAL,
      4         RANK() OVER ( ORDER BY AVG(SAL) DESC) RN
      5  FROM SCOTT.EMP
      6  GROUP BY DEPTNO)
      7  WHERE RN =1
      8  ;

        DEPTNO     AVGSAL     MINSAL
    ---------- ---------- ----------
            10 2916.66667       1300

    SQL>
     
  6. vldpriya

    vldpriya Active Member

    Messages:
    5
    Likes Received:
    1
    Trophy Points:
    65
    Hi Raj,

    As i studied in oracle there is a topic with nested group function.
    I dont know y its showing error.

    Kalir,

    Try this,

    SELECT top 1 Deptno, MIN(Salary) 'Lowest' ,AVG(salary) 'Avgsal' from emp
    group by deptno
    Order by Avgsal desc

    Please reply whether our understanding is correct.

    Thanks and Regards,
    Dhanapriya V L
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India

    What is "Top 1" clause in the SQL ? There is no such keyword in ORACLE.
     
  8. ora

    ora Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    select deptno from (select deptno, avg(sal),(select min(sal) from emp1) from emp1 group by deptno, sal order by avg(sal) desc) where rownum=1;

    Regards.....
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Why the query is grouped by sal along with depno? If it is grouped with sal , SAL and AVG(SAL) will be same.


    Code (SQL):
    SELECT deptno,sal, avg(sal)  FROM emp1 GROUP BY deptno, sal;
    here sal and avg(sal) will be same..