Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002

    Unanswered: problem with group by

    hello all,

    i am using odbc connection and it works fine, but i'm having trouble with select statement using group by. i want to display selected fields from 4 different table and it will display by grouping events.t_id.

    this my scripts

    $query = " select events.t_id, events.e_status, events.e_assignedto, events.e_id, ";
    $query .= " events.e_timestamp, tmpeid.e_id, category.c_name, ";
    $query .= " ticket.t_summary, ticket.t_category, ";
    $query .= " ticket.t_user, ticket.t_priority, ticket.t_timestamp_opened, ";
    $query .= " ticket.t_id2, ticket.t_id, COUNT (*)";
    $query .= " FROM events, tmpeid, category, ticket ";
    $query .= " WHERE ticket.t_id = events.t_id ";
    $query .= " AND events.e_id=tmpeid.e_id";
    $query .= " GROUP BY events.t_id";
    $query .= " HAVING COUNT(events.t_id) >= 1 ";

    this is error msg that i've found

    Warning: SQL error: [Oracle][ODBC][Ora]ORA-00979: not a GROUP BY expression , SQL state S1000 in SQLExecDirect in c:\apache\htdocs\scripts

    can anybody solve for me?????

  2. #2
    Join Date
    Jul 2001

    the problem is, that you use GROUP BY AND COUNT and do not specifiy what Oracle has to do with all the other fields in your SELECT statement.

    SELECT grade FROM scott.salgrade GROUP BY grade (works fine)

    SELECT grade, COUNT(losal) FROM scott.salgrade GROUP BY grade
    (also works fine, cause grade will be grouped and in every grouped record you will get a count of losal)

    SELECT grade, COUNR(losal), hisal FROM scott.salgrade GROUP BY grade

    (will raise an exception - cause Oracle does not know what to do with hisal in the grouped record)


    SELECT grade, COUNR(losal), SUM(hisal) FROM scott.salgrade GROUP BY grade

    (works also fine)

    So ... what you have to do is to kick out all the fields that has no group or agregate command and run the statement again.

    or ...

    group every field in the list

    Hope this helps

    Manfred Peter
    (Alligator Company)

Posting Permissions

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