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. Leo Anderson

    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. Laura

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

    That could probably a reason 😉

  4. Alan

    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…

  6. Gareth Roberts

    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

  7. Sokrates

    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 !

  9. Laurent Schneider Post author

    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

  10. Chen Shapira

    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!

  11. Bob Carlton

    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

  12. Sokrates

    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 ?

  13. Laurent Schneider Post author

    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

  15. Marco Gralike

    @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…

  17. AZOR

    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 .. 😉

  18. Laurent Schneider

    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

  19. Alexander Nikolaev

    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.

Comments are closed.