group by ()

I never saw neither used

group by ()

However, there are cases one may need it:

Support asked me in one of the issues I am working with them

what’s the size of your database ?

They didn’t define “size of database” – I don’t know what is meant by that.
All used space together ?
All segments together ?
All datafiles together ?
All datafiles + online-redo-log-files + controlfiles ?
All datafiles + online-redo-log-files + controlfiles + … (standby log-files, archived log files), rman backup’s ?
…

So, in order to gave them an answer, my first thought was querying SYS.FILE$, I thought this one should contain all necessary informations and if they aren’t content with me using SYS.FILE$ they can define for me what they want to know. I knew DBA_DATA_FILES is built using SYS.FILE$, and I thought SYS.FILE$ would probably contain also information about other files than datafiles …

SQL> desc sys.file$

 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE#					   NOT NULL NUMBER
 STATUS$				   NOT NULL NUMBER
 BLOCKS 				   NOT NULL NUMBER
 TS#						    NUMBER
....

shows, there are no BYTES in SYS.FILE$, so we have to sum up the Blocks and multiply by

select value from v$parameter where name = 'db_block_size';

, so my thoughts

( In fact, it is a bad idea to query SYS.FILE$ , it is not documented and my thoughts about it were completely wrong )

So I thought, I give them the output of

select sum(blocks) * (select value from v$parameter where name = 'db_block_size') from sys.file$;

I was very surprised when I saw:

SQL> select sum(blocks) * (select value from v$parameter where name = 'db_block_size') from sys.file$;

select sum(blocks) * (select value from v$parameter where name = 'db_block_size') from sys.file$                             *
ERROR at line 1:
ORA-00937: not a single-group group function

I didn’t expect that.

(select value from v$parameter where name = 'db_block_size')

is a scalar subquery, hence, for the duration of the SQL, it is a constant and should be treated like a constant.

Let’s go away from SYS-objects and use the following:
take an aggregate function

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) from n;

    MAX(N)
----------
	10

we can multiply that by a constant without trouble

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) * 5 from n;

  MAX(N)*5
----------
	50

but we are getting into trouble when using a scalar subquery instead of a constant:

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) * (select 5 from dual) from n;

select max(n) * (select 5 from dual) from n                        *

ERROR at line 2:
ORA-00937: not a single-group group function

Don’t know why and if this is expected / intended.
Scalar Subquery Expressions
shows some restrictions of scalar subquery expressions, but it doesn’t seem to cover this issue here ( I don’t use a scalar subquery expression in a group by clause )

Googling around, I found Laurent Schneider blogging on similar issues on
Why cannot I use subquery there?
and an interesting construct on Comment No.1 by Chen Shapira

group by ()

I never saw this construct, but tried it:

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) * (select 5 from dual) from n
  3  group by ();

MAX(N)*(SELECT5FROMDUAL)
------------------------
		      50

Eh voilà – it works.

group by ()

doesn’t seem to be documented (due to Laurent), so I don’t know if it is a good idea to use it in production, though.