Why this query requires group by clause in Oracle

user_2041124 0
Viewed 251

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?

subquery oracle sql
3 Answers

As far as i know, Your first query:

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

Sql will understand Count() as a column and (select count() from dual) as a second column => You must group by second column.

For example:

select count(*), (select count(1) from dual)from dual

It will throw the same error. But if you group by the second column it will be true. Example

select count(*), (select count(1) from dual)from dual
group by 1
user_80630480
Related