Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-00937: not a single-group group function in trivial query

Lukas EderAug 31 2021

This trivial query here doesn't seem to work:

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

Equivalent queries work in Db2, MySQL, PostgreSQL, SQL Server, and I don't see why it shouldn't, in Oracle. The scalar subquery has no correlation to the outer query, and thus should not affect the grouping at all.
In fact, this workarounds here make it work, hinting at the above being a bug:

select 
  count(*),
  (select count(*) from dual)
from dual
group by 1 -- alternatively group by grouping sets (())
Comments
Post Details
Added on Aug 31 2021
9 comments
1,725 views