CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Tundy
Tundy

Reputation: 194

Single column SUM, not a single-group group function

I would like to know why in this query:

SELECT 100 * (select sum(s.bytes)
                from dba_segments s
                where TABLESPACE_NAME='USERS') / sum(MAXBYTES) used
FROM dba_data_files
WHERE tablespace_name = 'USERS'

I'm getting

not a single-group group function error

I could fix it by adding GROUP BY tablespace_name at the end, but I don't understand why it's needed.

Also tried something like putting subselect inside of MAX but error changed to

Missing expression

Edit:

Expected result: 1 decimal between 0 and 100. (4.43725586 with results below) I wanted to divide results of 2 queries and multiple it by 100 at once.

First query to get actual size:

select sum(s.bytes)
from dba_segments s
where TABLESPACE_NAME='USERS'

Result: 524091392

Second query to get maximum size:

select sum(MAXBYTES)
FROM dba_data_files
WHERE tablespace_name = 'USERS'

Result: 1.1811E+10

Upvotes: 2

Views: 406

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think this is a parsing bug in Oracle. The following fails with the same message:

SELECT 100 * (select sum(s.bytes)
              from dba_segments s
             ) / sum(df.MAXBYTES) as used
FROM dba_data_files df
WHERE df.tablespace_name = 'USERS';

However, this fixes the issue:

SELECT 100 * (select sum(s.bytes)
                from dba_segments s
                where s.TABLESPACE_NAME = 'USERS'
             ) / sum(df.MAXBYTES) as used
FROM dba_data_files df
WHERE df.tablespace_name = 'USERS'
GROUP BY 'a';  -- a constant here

The subquery is uncorrelated, so it should be allowed. However, Oracle appears to be disallowing it in the version with no GROUP BY.

As for a solution, I would be inclined to move the two subqueries to the FROM clause:

SELECT (100 * segment_bytes / df_bytes) as used
FROM (select sum(df.MAXBYTES) as df_bytes
      from dba_data_files df
      where df.TABLESPACE_NAME = 'USERS'
     ) df CROSS JOIN
     (select sum(s.bytes) as segment_bytes
      from dba_segments s
      where s.TABLESPACE_NAME = 'USERS'
     ) s;

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

When using an embedded sub-select like this, Oracle isn't able to understand that you're pulling data for a single tablespace and insists on being given a GROUP BY.

If you change the query to use a join, with the tablespace name given in the WHERE clause, you can avoid the GROUP BY:

SELECT 100 * COALESCE(SUM(s.BYTES), SUM(f.BYTES))
         / COALESCE(NULLIF(SUM(f.MAXBYTES), 0), NULLIF(SUM(f.BYTES), 0)) used
  FROM DBA_DATA_FILES f
  LEFT OUTER JOIN DBA_SEGMENTS s
    ON s.TABLESPACE_NAME = f.TABLESPACE_NAME
  WHERE f.TABLESPACE_NAME = 'USERS'

I modified the initial query to handle the issue of SUM(f.MAXBYTES) coming out to zero, which it does on my system, which leads to a "divisor is zero" error.

Best of luck.

Upvotes: 1

user330315
user330315

Reputation:

I would use a JOIN between two derived tables instead:

select 100 * (s.used_bytes / df.maxbytes) as used_pct
from (
  select tablespace_name, sum(maxbytes) as maxbytes
  from dba_data_files 
  group by tablespace_name
) df
  join (
    select tablespace_name, sum(bytes) as used_bytes
    from dba_segments 
    group by tablespace_name
  ) s on s.tablespace_name = df.tablespace_name
where s.tablespace_name = 'USERS';

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

because your subselect is like you have a not aggregated result for the first column try using 

SELECT 100 * ( select sum(s.bytes) 
from dba_segments s 
where TABLESPACE_NAME='USERS')  / sum(MAXBYTES) used
FROM dba_data_files
WHERE tablespace_name = 'USERS'
group by 100 * ( select sum(s.bytes) 
from dba_segments s 
where TABLESPACE_NAME='USERS')

Upvotes: 0

Related Questions