Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Unanswered: not a GROUP BY expression

    hello all,

    when i run the following query

    Select MAX(BRANCH.NAME) BRNAME,
    MAX(CUSTOMER.CUSTNAME) CUSTNAME,
    MAX(Extn.Item_ID)
    from CUSTOMER,BRANCH,Order
    (Select OrdItm_A.Item_ID,
    from OrdItm OrdItm_A
    where
    OrdItm_A.OrdID=Order.OrdID
    ) Extn
    Where
    extn.OrdID=Order.OrdID
    Group by Branch.Branch_ID
    /

    It shows the following error though
    i have put all the fields in MAX condition

    Select MAX(BRANCH.NAME) BRNAME,
    *
    ERROR at line 1:
    ORA-00979: not a GROUP BY expression


    Thanx.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What happens if you eliminate the line "Group by Branch.Branch_ID" completely?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    1st, you don't have join criteria for BRANCH or CUSTOMER
    2nd, you're treating an in-line view as a correlated subquery
    3rd, in the main query, you join to the in-line view (extn) on OrdID, but this column is not selected in the in-line view

    So, remove the group by, remove the aggregates, fix the errors, realize it's a cartesian product, fix that, the do the grouping.

    BTW, a general guideline for "group by"s is to write them as regular selects with order by's, visually check the results - making sure that you didn't double up the number of records, and then put the group by in. This will save you from wondering why the sum's are wrong, and from complaining that "all group by's are slow".
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •