SQL experts: help working around ORA-00934: group function is not allowed here

SQL experts: help working around ORA-00934: group function is not allowed here

Post by Syltre » Wed, 11 Apr 2001 22:58:40



Considering this statement:

select  substr(tbspc.tablespace_name,1,15) "Tablespace",
        sum(distinct(fil.bytes/1024/1024)) "Total",
        round(sum(free.bytes/1024/1024)) "Free",
        round(sum(free.bytes) / tbspc.next_extent) "# extends",
        substr(file_name, 1, 50) "File Name"
from    dba_tablespaces tbspc,
        dba_free_space free,
        dba_data_files fil
where   fil.file_id = free.file_id
   and  fil.tablespace_name = tbspc.tablespace_name
group   by tbspc.tablespace_name,
        free.file_id,
        file_name,
        round(sum(free.bytes) / tbspc.next_extent);

SQL wants to have the round(sum(free.bytes) / tbspc.next_extent) in the
group by clause bu then, the sum() funcytion is not allowed.

How would you suggest I do this? The help on error 934 says I can use a
sub-select or having, but I don`t know how this can be done here.

Thanks!
--

Syltrem
http://pages.infinit.net/syltrem

 
 
 

SQL experts: help working around ORA-00934: group function is not allowed here

Post by Simon Irvi » Fri, 13 Apr 2001 23:01:58


I think the problem is that the round has to be inside the sum:
i.e.

select  substr(tbspc.tablespace_name,1,15) "Tablespace",
        substr(file_name, 1, 50) "File Name",
        sum(round(fil.bytes/1024/1024)) "Total",
        sum(round(free.bytes/1024/1024)) "Free",
        sum(round(free.bytes / tbspc.next_extent)) "# extends"
from    dba_tablespaces tbspc,
        dba_free_space free,
        dba_data_files fil
where   fil.file_id = free.file_id
   and  fil.tablespace_name = tbspc.tablespace_name
group   by tbspc.tablespace_name,
        free.file_id,
        file_name;

hope this is what you want

Simon Irvin


Quote:> Considering this statement:

> select  substr(tbspc.tablespace_name,1,15) "Tablespace",
>         sum(distinct(fil.bytes/1024/1024)) "Total",
>         round(sum(free.bytes/1024/1024)) "Free",
>         round(sum(free.bytes) / tbspc.next_extent) "# extends",
>         substr(file_name, 1, 50) "File Name"
> from    dba_tablespaces tbspc,
>         dba_free_space free,
>         dba_data_files fil
> where   fil.file_id = free.file_id
>    and  fil.tablespace_name = tbspc.tablespace_name
> group   by tbspc.tablespace_name,
>         free.file_id,
>         file_name,
>         round(sum(free.bytes) / tbspc.next_extent);

> SQL wants to have the round(sum(free.bytes) / tbspc.next_extent) in the
> group by clause bu then, the sum() funcytion is not allowed.

> How would you suggest I do this? The help on error 934 says I can use a
> sub-select or having, but I don`t know how this can be done here.

> Thanks!
> --

> Syltrem
> http://pages.infinit.net/syltrem


 
 
 

1. ORA-00934: group function is not allowed here

I am trying to insert a value based on the current max of a column, but am
getting the ORA-00934 error.

The statement I'm trying basically looks like:

INSERT INTO table (column) VALUES (nvl(max(column) + 1, 1))

(i.e. if no data, start at 1, otherwise add 1 to the current max)

So apparently it can't be done like this - how best can this be done without
the possibility that another client comes in in between calls (i.e. if I
first made a call to see what the max is, then the next call would be the
insert)

TIA,
mark

2. What does JDBC escape syntax do?

3. Access to Oracle (via Visual Basic) - ORA-00934

4. 30 Day Free copy of ABC Reporting

5. ORA-04044: procedure, function, package, or type is not allowed here

6. Processing NEXT file

7. SQL 7.0 Random function, does not work like SQL 6.5 Random function

8. Microsoft SQL isapi extension - 405 Method Not Allowed - POST queries are not allowed - OpenXML

9. ORA-25125 preventing import BUFFER POOL not allowed HELP

10. Need Help with Work around: Updatable Recordset on Access Form , sql server 2000

11. am I not allowed to use

12. SQL 7.0 Not allowing this update/join that worked in 6.5