plsql - Confusing group by syntax in oracle, how and why is this working? -

plsql - Confusing group by syntax in oracle, how and why is this working? -

i wanted grouping on nested select query result. can't understand how , why query runs fine.. right way utilize grouping on nested select column? on oracle 11g.

select min(updated_at), max(updated_at), (select count(*) domain_cdc app_context_id = 1561 , domain_cdc_status = 'done') done domain_cdc app_context_id = 1561 grouping '';

good question looks much shouldn't work @ first glance, should work without group by when dig bit deeper. looks bug, or @ to the lowest degree inconsistency, in parser.

the nested select isn't correlated needs executed once, making result constant, , documentation says:

in query containing grouping clause, elements of select list can aggregate functions, grouping expressions, constants, or expressions involving 1 of these.

if replaced actual count value wouldn't need grouping by:

select min(updated_at), max(updated_at), 42 done domain_cdc app_context_id = 1561;

... runs fine, , makes sense docs said. inconsistency nested select complain if instead:

select min(updated_at), max(updated_at), (select count(*) domain_cdc app_context_id = 1561 , domain_cdc_status = 'done') done domain_cdc app_context_id = 1561; sql error: ora-00937: not single-group grouping function

but not if include redundant group null. 1 perspective parser doesn't know nested select can treated constant , expects group by clause, know look doesn't need in group by.

if nested select correlated group null wouldn't work either:

select min(updated_at), max(updated_at), (select count(*) domain_cdc dc2 dc2.app_context_id = dc1.app_context_id , domain_cdc_status = 'done') done domain_cdc dc1 app_context_id = 1561 grouping null; sql error: ora-00979: not grouping look

so it's bit confused original query. weird, harmless think, , realise doesn't exclusively reply question... looks pretty similar bug 18697654 if that's help.

you don't need nested select here though, can utilize case statement:

select min(updated_at), max(updated_at), count(case when domain_cdc_status = 'done' updated_at end) done domain_cdc app_context_id = 1561;

count counts not-null values; case makes isn't 'done' null excluded aggregate. , since have no non-aggregate columns again, , parse can understand what's happening, don't need group by.

oracle plsql oracle11g