Why does Oracle say no GROUP BY clause?

Question

I am trying to retrieve data from an Oracle database table with the following conditions

• All records found by postal code must be grouped, sorted in descending order, descending.
• records found by city should be grouped, sorted alphabetically.
• All entries found by username must be grouped, sorted alphabetically.

To fulfill the conditions described above, I wrote a query as shown below

SELECT DISTINCT ba.uuid AS uuid , COUNT(*) over() AS rowcount FROM basicaddress ba WHERE ba.postalcode='143456' OR ba.lastname LIKE '%143456%' OR ba.city LIKE '%143456%' GROUP BY CASE WHEN ba.postalcode='143456' THEN ba.postalcode END ORDER BY CASE WHEN ba.postalcode='143456' THEN ba.postalcode END DESC, CASE WHEN ba.lastname LIKE '%143456%' THEN ba.lastname END ASC, CASE WHEN ba.city LIKE '%143456%' THEN ba.city ELSE ba.postalcode END DESC 

This query works fine in MYSQL, but in oracle it says "not a GROUP BY clause"

Any help would be greatly appreciated.

Answer 1

This only happens to you because MySQL violates the SQL logic.

Say we have an emp table:

 id ename dept 1 mark 10 2 John 10 3 Mary 10 4 Jane 20 

and request:

 select dept, ename from emp group by dept; 

will you get what? You should get two lines because there are two departments, but the request asks for ename. For 20 it’s clear, but for 10 the engine should return what?

It returns an error. I can’t guess what to give. Oracle removes the error - your error, but MySQL gets ename (not guaranteed). This is a mistake and may contain errors.

Correct queries:

 select dept, max(ename) --the latest, alaphabeticaly from emp group by dept; 

and

 --all enames and groups select dept, ename from emp group by dept, ename; 

After fixing this part you will have to decide

 COUNT(*) over() AS rowcount 

part. In oracle, AFAIK, you cannot mix analytic functions with a query group.

Answer 2

The specific answer to your question is that uuid not in the expression group. Other answers have already explained the MySQL (mis) function that allows this to happen. Correcting the request is easy:

 SELECT ba.uuid AS uuid, COUNT(*) over() AS rowcount FROM basicaddress ba WHERE ba.postalcode='143456' OR ba.lastname LIKE '%143456%' OR ba.city LIKE '%143456%' GROUP BY uuid, CASE WHEN ba.postalcode='143456' THEN ba.postalcode END ORDER BY max(CASE WHEN ba.postalcode='143456' THEN ba.postalcode END) DESC, max(CASE WHEN ba.lastname LIKE '%143456%' THEN ba.lastname END) ASC, max(CASE WHEN ba.city LIKE '%143456%' THEN ba.city ELSE ba.postalcode END) 

I also removed the “separate”, which should be redundant when using the group.

However, I do not see how this satisfies your original question. I would expect something more in the following lines:

 select (case when postalcode is not null then postalcode when city is not null then city when lastname is not null then lastname end), (case when postalcode is not null then 1 when city is not null then 2 when lastname is not null then 3 end), count(*) from basicaddress ba group by (case when postalcode is not null then postalcode when city is not null then city when lastname is not null then lastname end), (case when postalcode is not null then 1 when city is not null then 2 when lastname is not null then 3 end) order by 1, 2 

This may not be exactly what you need, but it should point you in the right direction. In addition, it is standard SQL and will work in both databases.

Answer 3

It works in MySQl because MySQL has a rough interpretation of SQL.

In any regular dialect, something in the SELECT or ORDER BY must be either an aggregate function or a GROUPed expression.

At least you will need to group by ba.uuid, lastname and city.

Answer 4

I do not know about MySQL, but after working in Oracle 11g:

 With T_PATTERN as (select '%143456%' as PATTERN from DUAL) , T_INPUT as ( select 1 as UUID, 'X143456y' as FIRSTNAME, 'last1' as LASTNAME, 'u143456v' as CITY, 'w143456z' as POSTALCODE from DUAL union select 2 as UUID, 'first2' as FIRSTNAME, 'a143456b' as LASTNAME, 'a143456b' as CITY, 'postal2' as POSTALCODE from DUAL union select 3 as UUID, 'first3' as FIRSTNAME, 'last3' as LASTNAME, 'c143456d' as CITY, 'postal3' as POSTALCODE from DUAL union select 4 as UUID, 'first4' as FIRSTNAME, 'last4' as LASTNAME, 'city4' as CITY, 'postal4' as POSTALCODE from DUAL ), Q_FNAME as ( select TI.UUID, TI.FIRSTNAME, COUNT(*) FN_COUNT from T_INPUT TI inner join T_PATTERN TP on 1=1 where UPPER(TI.FIRSTNAME) like TP.PATTERN GROUP BY TI.UUID, TI.FIRSTNAME ), Q_LNAME as ( select TI.UUID, TI.LASTNAME, COUNT(*) LN_COUNT from T_INPUT TI inner join T_PATTERN TP on 1=1 where UPPER(TI.LASTNAME) like TP.PATTERN group by TI.UUID, TI.LASTNAME ), Q_CITY as ( select TI.UUID, TI.CITY, COUNT(*) CT_COUNT from T_INPUT TI inner join T_PATTERN TP on 1=1 where UPPER(TI.CITY) like TP.PATTERN group by TI.UUID, TI.CITY ), Q_PCODE as ( select TI.UUID, TI.POSTALCODE, COUNT(*) PC_COUNT from T_INPUT TI inner join T_PATTERN TP on 1=1 where UPPER(TI.POSTALCODE) like TP.PATTERN group by TI.UUID, TI.POSTALCODE ) select TI.UUID, TI.FIRSTNAME, TI.LASTNAME, TI.CITY, TI.POSTALCODE, QF.FN_COUNT, QL.LN_COUNT, QC.CT_COUNT, QP.PC_COUNT from T_INPUT ti left join Q_FNAME QF on TI.UUID=QF.UUID left join Q_LNAME QL on TI.UUID=QL.UUID left join Q_CITY QC on TI.UUID=QC.UUID left join Q_PCODE QP on TI.UUID=QP.UUID order by ti.UUID;