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 - not a group by expression

Discussion in 'SQL PL/SQL' started by lcd, Feb 2, 2015.

  1. lcd

    lcd Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I have this query and give this error not a group by expression , can someone help me on this matter? thank you

    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
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Why you used Having here?

    why don't you use those and conditions in where clause?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    HAVING is used with GROUP BY, nowhere else. For non-group-by queries WHERE is the clause to use.
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Well there's actually two problems. First, you wouldn't use DISTINCT with an aggregate function. The GROUP BY operation is already going to give you distinct values. Second, as has been noted, you are misusing the HAVING clause.

    As Zargon indicated, HAVING is only use when a SQL statement contains a GROUP BY clause. Since your SQL has one, his statement may be a little confusing. Specifically, a SQL statement with a GROUP BY clause can have both a WHERE clause and a HAVING clause. The WHERE clause is used to filter rows from the query before the grouping operation and the HAVING clause is used to filter groups of rows after the grouping operation. Normally you would use HAVING if you needed a filter that referenced aggregated data like: "HAVING SUM(patram.amount) > 400". It's often possible to have a given filter condition in either clause, but generally if something can be done from the WHERE clause, it should be so the rows are removed earlier in the SQL operation.

    Your statement should be something more like:

    Code (Text):
    SELECT 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'
    AND    panumbt.payroll_code = patram.payroll_code
    AND    patram.payroll_code = patram_header.payroll_code
    GROUP BY patram.emplcode,
             patram.GL_ACCOUNT,
             patram.payroll_code,
             patram.TYPE;