Why this query requires group by clause in Oracle

Question

can someone explain me why this query requires GROUP BY clause in Oracle while is perfectly fine in MSSQL, MySQL, Postgresql?

select count(*) / (select count(*) from dual)from dual

Oracle complains

"[Error] Execution (1: 27): ORA-00937: not a single-group group function"

If I change it and add dummy group by like this

select count(*) / (select count(*) from dual)from dual
group by null

then it's fine for Oracle. Why Oracle requires this group by?

Answer 1

The format of your query is -

SELECT aggregate_function()/(subquery) FROM table;

While parsing the query for the syntax check, Oracle can not tell whether the subquery is a correlated subquery or a non-correlated subquery.

If it is a correlated subquery then the result of the subquery will be dependent on each row of the 'table' i.e. the result of the subquery may be different for each record.

For the sake of understanding, consider the subquery as a variable.
Since the aggregate_function is used along with a variable, the value of which may differ for each record - Oracle expects the query to have a group by clause.

Example

Table: tab1

c1 | c2

A1 | 1

A2 | 1

B1 | 2

C1 | 3

Table: tab2

c3 | c4

X1 | 1

X2 | 2

Y1 | 1

Z1 | 1

Query:

_SELECT count(*)/(SELECT count(*) FROM tab2 t2 WHERE t2.c4 <= t1.c2) FROM tab1 t1;_

It wouldn't work. I hope you agree.

Why it may work in MSSQL, MySQL or Postgresql - I am not sure. Probably they are pro-actively checking whether the subquery is a correlated subquery or a non-correlated subquery. If it is not a correlated subquery, they are allowing the execution.