ORA-00979: not a GROUP BY expression for Oracle but not valid for MySQL in context of clause difference

Question

I have run this query in Oracle

select studentid, attndmark
from attendance_master m,
     attendance_detail d
where m.attnid = d.attendid
group by studentid

and got the error:

ORA-00979: not a GROUP BY expression

The error is fine and I know the issue of column list in select clause. But similar query is valid in MySQL.

SELECT aff.akey, username
FROM `affiliates` aff,
     affstats ast
WHERE aff.akey = ast.akey
group by aff.akey

I need a query trick that can run on both RDBMS Oracle/ Mysql and also MSSQL.

What could be the trick?

Answer 1

MySQL is wrong, in the sense that it does not conform to the SQL standard (or even common sense in this case). It allows columns in the SELECT that are not arguments to aggregation functions and that are not in the GROUP BY. The documentation is explicit that the values come from "indeterminate" rows.

By the way, you should learn proper explicit JOIN syntax. The query can be written as:

SELECT aff.akey, MAX(username)
FROM affiliates aff JOIN
     affstats ast 
     ON aff.akey=ast.akey
GROUP BY aff.akey;

This will work in both databases.