problem with group by [Archive]

eg4

04-30-02, 06:03

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?????

alligatorsql.com

04-30-02, 07:48

Hello,

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.
f.e

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)

a

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)
http://www.alligatorsql.com