group by clause didn't work
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: group by clause didn't work

  1. #1
    Join Date
    Oct 2002
    Location
    CA
    Posts
    67

    group by clause didn't work

    I recently installed a 9i database, however, I have noticed my group by clause didn't work right by some strange reasons.

    SQL> l
    1 select owner,object_type,count(*) from
    2* dba_objects group by owner,object_type

    OWNER OBJECT_TYPE COUNT(*)
    ------------------------------ ------------------ ----------
    RAM SYNONYM 51
    SYS LOB 28
    SYS TYPE 465
    SYS VIEW 2087
    SYS INDEX 311
    SYS QUEUE 4
    SYS TABLE 341
    SYS CLUSTER 10
    SYS CONTEXT 1
    SYS LIBRARY 62
    SYS PACKAGE 342

    OWNER OBJECT_TYPE COUNT(*)
    ------------------------------ ------------------ ----------
    SYS SYNONYM 6
    SYS TRIGGER 10
    SYS FUNCTION 41
    SYS OPERATOR 1
    SYS SEQUENCE 41
    SYS JAVA DATA 293
    SYS PROCEDURE 25
    SYS TYPE BODY 23
    SYS JAVA CLASS 8789
    SYS JAVA SOURCE 8
    SYS PACKAGE BODY 318

    OWNER OBJECT_TYPE COUNT(*)
    ------------------------------ ------------------ ----------
    SYS JAVA RESOURCE 173
    SYS RESOURCE PLAN 3
    SYS CONSUMER GROUP 4
    SYS EVALUATION CONTEXT 1
    HIEN TABLE 4
    HIEN SYNONYM 51
    --------------------------------------
    It's not what the world does to you that matters. It's how you respond...

  2. #2
    Join Date
    Oct 2002
    Location
    CA
    Posts
    67

    Clarification - the order of records is wrong

    Just for a clarification, what I mean is that the sort order of records is wrong.
    --------------------------------------
    It's not what the world does to you that matters. It's how you respond...

  3. #3
    How do you mean - what was wrong?

    -B

  4. #4
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Whats wrong with that?

  5. #5
    Join Date
    Oct 2002
    Location
    CA
    Posts
    67
    HIEN should come before RAM and SYS. Does GROUP BY also order the records for you?
    It works only on the 8.17 environment I have listed below.

    8.17 environment
    -----------------------

    SQL> select owner,object_type,count(*) from
    2 dba_objects group by owner,object_type;

    OWNER OBJECT_TYPE COUNT(*)
    ------------------------------ ------------------ ----------
    ALUMNI FUNCTION 15
    ALUMNI INDEX 380
    ALUMNI PROCEDURE 68
    ALUMNI TABLE 320
    ALUMNI TRIGGER 4
    BANINST1 FUNCTION 190
    BANINST1 PACKAGE 199
    BANINST1 PACKAGE BODY 198
    BANINST1 PROCEDURE 21
    BANINST1 VIEW 138
    BANSECR INDEX 7

    OWNER OBJECT_TYPE COUNT(*)
    ------------------------------ ------------------ ----------
    BANSECR PACKAGE 3
    BANSECR PACKAGE BODY 3
    BANSECR SEQUENCE 1
    BANSECR TABLE 8
    BANSECR VIEW 2
    DBSNMP SYNONYM 4
    GENERAL INDEX 209
    GENERAL SEQUENCE 6
    GENERAL TABLE 199
    GENERAL TRIGGER 23
    GENERAL VIEW 2

    OWNER OBJECT_TYPE COUNT(*)
    ------------------------------ ------------------ ----------
    HIEN SYNONYM 51
    HIEN TABLE 4
    JAMES TABLE 1
    MBOOTE INDEX 1
    MBOOTE TABLE
    --------------------------------------
    It's not what the world does to you that matters. It's how you respond...

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    You should not expect them to be sorted. Under 7.3.4 you got the sort as a free bonus - from 8.something you have to include the "order by".

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by james_cc
    It works only on the 8.17 environment I have listed below.
    That is luck. In general in 8.1.7 you need "order by"

  8. #8
    Join Date
    Oct 2002
    Location
    CA
    Posts
    67
    Just curious, how can I easily combine group by and order by in order to get the count(*) and sort order to work at the same time? Since I have never used the two together that would work, unless I rewrite the query using field alias etc.


    1 select owner,object_type,count(*) from
    2* dba_objects group by owner,object_type
    --------------------------------------
    It's not what the world does to you that matters. It's how you respond...

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    select owner, object_type, count(*)
    from dba_objects
    group by owner,object_type
    order by owner,object_type

    OR

    order by 1,2

  10. #10
    Join Date
    Oct 2002
    Location
    CA
    Posts
    67
    Thanks Dapi
    --------------------------------------
    It's not what the world does to you that matters. It's how you respond...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width