Login or Register to Ask a Question and Join Our Community


Programming

Sql ORA-00937: not a single-group group function

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming Sql ORA-00937: not a single-group group function
# 1  
Old 02-04-2015
Sql ORA-00937: not a single-group group function
I'm trying to return only one row with the highest value for PCT_MAX_USED. Any suggestions?

When I add this code, I get the ORA-00937 error.
Code:
trunc(max(decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100))) pct_max_used

This is the original and returns all rows.
Code:
select (select decode(extent_management,'LOCAL','*',' ')
         from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
         nvl(a.tablespace_name,'UNKOWN')) name,
      (decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100)) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
             max(bytes)/1024 largest,
             tablespace_name
      from  sys.dba_free_space
      group by tablespace_name, bytes) a,
    ( select sum(bytes)/1024 Kbytes_alloc,
             sum(maxbytes)/1024 Kbytes_max,
             tablespace_name
      from sys.dba_data_files
      group by tablespace_name
      union all
     select sum(bytes)/1024 Kbytes_alloc,
             sum(maxbytes)/1024 Kbytes_max,
             tablespace_name
      from sys.dba_temp_files
      group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name

HTML Code:
*SYSAUX         	1.34277407778458
*SYSAUX         	1.34277407778458
*UNDOTBS1         	6.11114793355366
*BOB_DATA         	0.0183105468877898
*BOB_INDEX         	0.016242265712639
*BOB_DATA_2010         	7.450580602128E-5
*BOB_DATA_2005         	7.450580602128E-5
*BOB_DATA_2001         	7.450580602128E-5
*BOB_SUBSET         	       0.0480651855804481
Last edited by rbatte1; 02-13-2015 at 08:16 AM..
# 2  
Old 02-06-2015
Move dba_tablespaces to the from clause and group by the fields that are not aggregated. b.tablespace_name is never null, so nvl is superfluous:
Code:
select decode(c.extent_management,'LOCAL','*',' ') || b.tablespace_name name,
       max(trunc((decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100)))) pct_max_used
  from (select sum(bytes)/1024 Kbytes_free,
               max(bytes)/1024 largest,
               tablespace_name
          from sys.dba_free_space
         group by tablespace_name, bytes) a,
       (select sum(bytes)/1024 Kbytes_alloc,
               sum(maxbytes)/1024 Kbytes_max,
               tablespace_name
          from sys.dba_data_files
         group by tablespace_name
        union all
        select sum(bytes)/1024 Kbytes_alloc,
               sum(maxbytes)/1024 Kbytes_max,
               tablespace_name
          from sys.dba_temp_files
         group by tablespace_name )b,
       dba_tablespaces c 
 where a.tablespace_name (+) = b.tablespace_name
   and c.tablespace_name (+) = b.tablespace_name
 group by decode(c.extent_management,'LOCAL','*',' ') || b.tablespace_name

These 2 Users Gave Thanks to cero For This Post:
jim mcnamara rbatte1
# 3  
Old 02-12-2015
Thank you, that was a great answer.

I'm trying to get the truncated maximum single value from the above query.

I"m trying to find this:
Code:
BOB_DATA_2010         	7

I tried using order by and rownum = 1 but it didn't work.

Any ideas?
Last edited by rbatte1; 02-13-2015 at 08:17 AM.. Reason: Added CODE & ICODE tags
# 4  
Old 02-13-2015
What does didn't work mean? You get wrong results or a syntax error? It works for me:
Code:
select * from
  (select decode(c.extent_management,'LOCAL','*',' ') || b.tablespace_name name,
         max(trunc((decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100)))) pct_max_used
    from (select sum(bytes)/1024 Kbytes_free,
                 max(bytes)/1024 largest,
                 tablespace_name
            from sys.dba_free_space
           group by tablespace_name, bytes) a,
         (select sum(bytes)/1024 Kbytes_alloc,
                 sum(maxbytes)/1024 Kbytes_max,
                 tablespace_name
            from sys.dba_data_files
           group by tablespace_name
          union all
          select sum(bytes)/1024 Kbytes_alloc,
                 sum(maxbytes)/1024 Kbytes_max,
                 tablespace_name
            from sys.dba_temp_files
           group by tablespace_name )b,
         dba_tablespaces c
   where a.tablespace_name (+) = b.tablespace_name
     and c.tablespace_name (+) = b.tablespace_name
   group by decode(c.extent_management,'LOCAL','*',' ') || b.tablespace_name
   order by pct_max_used desc)
 where rownum = 1

You have to use another layer of subquery because order by is applied last to the result set.
This User Gave Thanks to cero For This Post:
progkcp
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

need a one liner to grep a group info from /etc/group and use that result to search passwd file

/etc/group tiadm::345:mk789,po312,jo343,ju454,ko453,yx879,iy345,hn453 bin::2:root,daemon sys::3:root,bin,adm adm::4:root,daemon uucp::5:root /etc/passwd mk789:x:234:1::/export/home/dummy:/bin/sh po312:x:234:1::/export/home/dummy:/bin/sh ju454:x:234:1::/export/home/dummy:/bin/sh... (6 Replies)
Discussion started by: chidori
6 Replies

2. AIX

Adding a Volume Group to an HACMP Resource Group?

Hi, I have a 2 node Cluster. Which is working in active/passive mode (i.e Node#1 is running and when it goes down the Node#2 takes over) Now there's this requirement that we need a mount point say /test that should be available in active node #1 and when node #1 goes down and node#2 takes... (6 Replies)
Discussion started by: aixromeo
6 Replies

3. Shell Programming and Scripting

Sort the file contents in each group....print the group title as well

I've this file and need to sort the data in each group File would look like this ... cat file1.txt Reason : ABC 12345-0023 32123-5400 32442-5333 Reason : DEF 42523-3453 23345-3311 Reason : HIJ 454553-0001 I would like to sort each group on the last 4 fileds and print them... (11 Replies)
Discussion started by: prash184u
11 Replies

4. Shell Programming and Scripting

Merge group numbers and add a column containing group names

Hi All I do have a file like this with 6 columns. Groups of data merge together and the group number is indicated above each group. 1 1 12 26 289 3.2e-027 GCGTATGGCGGC 2 12 26 215 6.7e+006 TTCCACCTTTTG 3 9 26 175 ... (1 Reply)
Discussion started by: Lucky Ali
1 Replies

5. Shell Programming and Scripting

Merge group numbers and add a column containing group names

I have a file in the following format. Groups of data merge together and the group number is indicated above each group. 1 adrf dfgr dfg 2 dfgr dfgr 3 dfef dfr fd 4 fgrt fgr fgg 5 fgrt fgr (3 Replies)
Discussion started by: Lucky Ali
3 Replies

6. UNIX for Advanced & Expert Users

is it possible to have a 1000 user in single group

I m usi ng RHEL 4 and /etc/group file contains line 7000 characters. so it is possible to have a 1000 user in single group (5 Replies)
Discussion started by: amitpansuria
5 Replies

7. UNIX for Dummies Questions & Answers

How many user can be added to single group

Hi There, How many user can be added to a unix single group. I need this for unix and solaris. BRs -----Post Update----- I'm asking about secondary group and not primary group. All the users are having 8 character as their username. value is set for getconf LINE_MAX is... (1 Reply)
Discussion started by: maestromani
1 Replies

8. Shell Programming and Scripting

Substitute to GROUP BY function

Hi All, I really need a help on this thing. Most of us are aware about the group by function in Oracle. Do we have a substitute ( not necessarily a single line command) to it in Unix? Let me put it this way. I have a file whose content is like file1-: ID1,ID2,ID3,ID4,ID5 1,2,3,123,5... (3 Replies)
Discussion started by: rinku11
3 Replies

9. Solaris

entry in /etc/group too long - problem using sudo with %group

hi folks, I've been googling for quite some time, but still can't find anything near it...my problem is the following: for useradministration in our company we are using ssh/sudo, now whenever I try to add users (we have quite a number of users) with useradd -G groupname for secondary group I... (4 Replies)
Discussion started by: poli
4 Replies
Login or Register to Ask a Question