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!

ERROR at line 1 ORA-00979: not a GROUP BY expression

Discussion in 'SQL PL/SQL' started by raidermegaman, Nov 1, 2011.

  1. raidermegaman

    raidermegaman Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have used these variations, but I still can't get rid of the error
    SELECT TeachName
    FROM pulsiferc1_teachers
    GROUP By DeptNo
    HAVING NOT (DeptNo = 12);

    SELECT DeptNo, TeachName
    FROM pulsiferc1_teachers
    GROUP By DeptNo
    HAVING NOT (DeptNo = 12);

    SELECT TeachName, DeptNo
    FROM pulsiferc1_teachers
    GROUP By DeptNo
    HAVING NOT (DeptNo = 12);
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why are you trying to use HAVING when you haven't computed any aggregates? It CAN be done but WHERE is a more appropriate construct:

    Code (SQL):
    SQL> SELECT TeachName
    2 FROM pulsiferc1_teacher
    3 GROUP BY DeptNo
    4 HAVING NOT (DeptNo = 12);
    SELECT TeachName
    *
    ERROR at line 1:
    ORA-00979: NOT a GROUP BY expression
     
    SQL>
    SQL> SELECT DeptNo, TeachName
    2 FROM pulsiferc1_teacher
    3 GROUP BY DeptNo
    4 HAVING NOT (DeptNo = 12);
    SELECT DeptNo, TeachName
    *
    ERROR at line 1:
    ORA-00979: NOT a GROUP BY expression
     
    SQL>
    SQL> SELECT TeachName, DeptNo
    2 FROM pulsiferc1_teacher
    3 GROUP BY DeptNo
    4 HAVING NOT (DeptNo = 12);
    SELECT TeachName, DeptNo
    *
    ERROR at line 1:
    ORA-00979: NOT a GROUP BY expression
     
    SQL>
    SQL> SELECT TeachName
    2 FROM pulsiferc1_teacher
    3 GROUP BY TeachName
    4 HAVING NOT (DeptNo = 12);
    HAVING NOT (DeptNo = 12)
    *
    ERROR at line 4:
    ORA-00979: NOT a GROUP BY expression
     
    SQL>
    SQL> SELECT DeptNo, TeachName
    2 FROM pulsiferc1_teacher
    3 GROUP BY DeptNo,TeachName
    4 HAVING NOT (DeptNo = 12);
     
    DEPTNO TEACHNAME
    ---------- ----------------------------------------
    9 Dimples McSweeny
    2 Dimples McSweeny
    1 Dimples McSweeny
    5 Dimples McSweeny
    6 Dimples McSweeny
    0 Dimples McSweeny
    3 Dimples McSweeny
    10 Dimples McSweeny
    4 Dimples McSweeny
    7 Dimples McSweeny
    11 Dimples McSweeny
     
    DEPTNO TEACHNAME
    ---------- ----------------------------------------
    8 Dimples McSweeny
     
    12 ROWS selected.
     
    SQL>
    SQL> SELECT TeachName, DeptNo
    2 FROM pulsiferc1_teacher
    3 GROUP BY TeachName,DeptNo
    4 HAVING NOT (DeptNo = 12);
     
    TEACHNAME DEPTNO
    ---------------------------------------- ----------
    Dimples McSweeny 1
    Dimples McSweeny 8
    Dimples McSweeny 4
    Dimples McSweeny 11
    Dimples McSweeny 2
    Dimples McSweeny 7
    Dimples McSweeny 0
    Dimples McSweeny 9
    Dimples McSweeny 6
    Dimples McSweeny 3
    Dimples McSweeny 5
     
    TEACHNAME DEPTNO
    ---------------------------------------- ----------
    Dimples McSweeny 10
     
    12 ROWS selected.
     
    SQL>
    SQL> SELECT TeachName
    2 FROM pulsiferc1_teacher
    3 WHERE NOT (DeptNo = 12);
     
    TEACHNAME
    ----------------------------------------
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
     
    TEACHNAME
    ----------------------------------------
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
    Dimples McSweeny
     
    19 ROWS selected.
     
    SQL>
    SQL> SELECT DeptNo, TeachName
    2 FROM pulsiferc1_teacher
    3 WHERE NOT (DeptNo = 12);
     
    DEPTNO TEACHNAME
    ---------- ----------------------------------------
    1 Dimples McSweeny
    2 Dimples McSweeny
    3 Dimples McSweeny
    4 Dimples McSweeny
    5 Dimples McSweeny
    6 Dimples McSweeny
    7 Dimples McSweeny
    8 Dimples McSweeny
    9 Dimples McSweeny
    10 Dimples McSweeny
    11 Dimples McSweeny
     
    DEPTNO TEACHNAME
    ---------- ----------------------------------------
    0 Dimples McSweeny
    1 Dimples McSweeny
    2 Dimples McSweeny
    3 Dimples McSweeny
    4 Dimples McSweeny
    5 Dimples McSweeny
    6 Dimples McSweeny
    7 Dimples McSweeny
     
    19 ROWS selected.
     
    SQL>
    SQL> SELECT TeachName, DeptNo
    2 FROM pulsiferc1_teacher
    3 WHERE NOT (DeptNo = 12);
     
    TEACHNAME DEPTNO
    ---------------------------------------- ----------
    Dimples McSweeny 1
    Dimples McSweeny 2
    Dimples McSweeny 3
    Dimples McSweeny 4
    Dimples McSweeny 5
    Dimples McSweeny 6
    Dimples McSweeny 7
    Dimples McSweeny 8
    Dimples McSweeny 9
    Dimples McSweeny 10
    Dimples McSweeny 11
     
    TEACHNAME DEPTNO
    ---------------------------------------- ----------
    Dimples McSweeny 0
    Dimples McSweeny 1
    Dimples McSweeny 2
    Dimples McSweeny 3
    Dimples McSweeny 4
    Dimples McSweeny 5
    Dimples McSweeny 6
    Dimples McSweeny 7
     
    19 ROWS selected.
     
    SQL>
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Ypu can have a look at the usage of GROUP BY and WHEREclauses.
     
  4. lcd

    lcd Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I have the same issue - this is my query

    SELECT DISTINCT patram.emplcode emplcode,
    patram.GL_ACCOUNT,
    patram.payroll_code,
    SUM(patram.amount) amount,
    SUM(patram.hours) hours,
    patram.TYPE
    FROM hrsense1.panumbt,
    hrsense1.patrde,
    hrsense1.patram_header,
    hrsense1.patram
    WHERE
    patram.emplcode = patram_header.emplcode
    AND patram.TYPE = patrde.TYPE
    AND patram_header.status = 'A'
    GROUP BY patram.emplcode,
    patram.GL_ACCOUNT,
    patram.payroll_code,
    patram.TYPE
    HAVING panumbt.payroll_code = patram.payroll_code
    AND patram.payroll_code = patram_header.payroll_code
     
  5. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    GROUP BY Having Clause can be used to limit the returned rows after the grouping. Oracle GROUP BY HAVING can be used in conjunction with other logical functions such as MIN, MAX, COUNT, and SUM. Here it is used for joining 2 columns.