sql - Why Oracle is saying not a GROUP BY expression?

Question

I am Trying to retrive data from the Oracle database table with following conditions

1. All entries found by zip-code should be grouped, sorted by zip-code descending.
2. entries found by city should be grouped, sorted alphabetically.
3. All entries found by dealername should be grouped, sorted alphabetically.

To staisfy the above conditions I wrote the query like 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 is working fine in MYSQL but in oracle it is saying "not a GROUP BY expression"

Any help will be greatly appreciated

Answer 1

This happens to you just because MySQL breaks the logic of SQL.

Let's say we have table emp:

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

and the query:

select dept, ename
from emp 
group by dept;

you'll get what? You should get two lines, because there are two departments, but the query ask for ename. For 20 is clear but for 10 the engine should return what?

It shoud return an error. Can't guess what ename to give. Oracle shoot an error - your error, but MySQL gets an ename(not guaranteed which). That's missleading and may conduct bugs.

Correct queries would be:

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 you correct this part, you'll have to resolve the

COUNT(*) over() AS rowcount

part. In oracle, AFAIK, you can't mix analytic functions with group by queries.