not a group by expression?

How do I count all objects in the current schema?


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 1 09:58:46 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle 12g Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Expensive option

SQL> select
  2    user,
  3    count(*)
  4  from
  5    user_objects
  6  where
  7    status='INVALID';
  user,
  *
ERROR at line 2:
ORA-00937: not a single-group group function

Disconnected from Oracle 12g Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Expensive option

It does not seem to work … What’s wrong?

19 thoughts on “not a group by expression?”

  1. Hi Laurent,

    Either you are very very tired, or I’ve missed something ?!
    select
    user,
    count(*)
    from
    user_objects
    where
    status=’INVALID’
    group by user

  2. Disconnected from Oracle 12g Enterprise Edition Release 12.1.0.1.0 – 64bit Production With the Expensive option :)))))

    That could probably a reason ;)

  3. Laurent,

    I love the version banner, I take it that’s hacked and not a beta version your working with.

    The “Expensive Option” bit is particularly amusing.

    Thanks,
    Alan…

  4. Hey Laurent,

    You forgot:
    alter session set automatic_group_clause = true;

    PS. I’ve been wanting that alter session command since Oracle 7.3!

    Gareth

  5. what’s wrong ?
    well, beside “Oracle 12g …”
    it is wrong that you would never get
    ORA-00937: not a single-group group function

    probably if you would ask for
    1 select
    2 object_type,
    3 count(*)
    4 from
    5 user_objects
    6 where
    7 status=’INVALID’

    but not if you asked for
    1 select
    2 user,
    3 count(*)
    4 from
    5 user_objects
    6 where
    7 status=’INVALID’

    happyu April’s fools day !

  6. happy april fool of course :-)

    The query is perfectly fine to count all invalid objects in the current schema. The error and banner were fake of course :mrgreen:

    Adding a GROUP BY clause will suppress the output when no invalid object exists…

    
    SQL> select
      2       user,
      3       count(*)
      4  from
      5       user_objects
      6  where
      7       status='INVALID'
      8  ;
    
    USER                             COUNT(*)
    ------------------------------ ----------
    SCOTT                                   0
    
    SQL> select
      2       user,
      3       count(*)
      4  from
      5       user_objects
      6  where
      7       status='INVALID'
      8  group by
      9       user;
    
    no rows selected
    

  7. Hey Laurent, what the Sum of Ten + Five? Please put easier spam protection, took me 15 minutes to figure this out :)

    Your post made my day!

  8. worked for me after I applied the patch.

    SQL*Plus: Release 10.2.0.1.0 – Production on Wed Apr 1 09:58:46 2009

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Connected to:
    Oracle 12g Enterprise Edition Release 12.1.0.1.0 – 64bit Production
    With the Expensive option

    SQL> select
    2 user,
    3 count(*)
    4 from
    5 user_objects
    6 where
    7 status=’INVALID';

    Query finished, retrieving results…
    USER COUNT(*)
    —————————— ————————————–

    0 row(s) retrieved

    Disconnected from Oracle 12g Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    With the Expensive option

  9. Laurent Schneider :
    happy april fool of course
    The query is perfectly fine to count all invalid objects in the current schema. The error and banner were fake of course

    Adding a GROUP BY clause will suppress the output when no invalid object exists…

    em ?
    sorry, but I don’t understand it does not work for me:

    sql > select user,count(*) from dual group by user;

    USER COUNT(*)
    —————————— ———-
    SOKRATES 1

    sql > select count(*) from dual where dummy=’X';

    COUNT(*)
    ———-
    1

    sql > select user,count(*) from dual where dummy=’X’ group by user;

    USER COUNT(*)
    —————————— ———-
    SOKRATES 1

    sql > select user,count(*) from user_objects where status=’INVALID';

    USER COUNT(*)
    —————————— ———-
    SOKRATES 2

    sql > select user,count(*) from user_objects where status=’INVALID’ group by user;

    USER COUNT(*)
    —————————— ———-
    SOKRATES 2

    something wrong with my database ?

  10. Sokrates, if you have count(*)=2, the “GROUP BY whateverfunction” will give you 2. But if you have 0 row, the “GROUP BY whateverfunction” will give you “no rows returned” instead of 0

  11. @Laurent Schneider

    Great example why SQL is sometimes so misleading, Chris Date would have loved this example.

    What do you want today?

    “1 row selected” or “no rows selected”.

    Great example as a basis for a “Nullology” / “SQL is bad” discussion…

  12. It if of course wrong ;) It is not group function. User is not variable, but function, you can not use group by when rows can be different and you have no this column in group by. That is right. USER = not variable, function, it same as you can select this:

    select
    dbms_random.random,
    count(*)
    from
    user_objects
    where
    status=’INVALID';

    I m on this site about 100x times – everytime I found this page, when i m looking for information about Oracle 12c db .. ;)

  13. I am not part of the beta but I could imagine the next generation database being a hot topic in OOW 2012 :)

    What’s wrong?

    
    select dbms_random.random,count(*)
    from user_objects
    where status='XXX'
    group by dbms_random.random;
    
    no data found
    

  14. I got quite a good laugh out of this before reading all the comments and understanding the nuances of the user function.

    Thank you, this post is extremely informative and revealing.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>