DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: count on table

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    count on table

    have this table OCC

    COD.............NAME
    001.............TOM
    001.............SAM
    002.............CAT
    003.............DOG
    003.............SAM
    003.............TOM
    004.............JACK
    005.............TOM
    005.............SAM
    ........................
    ........................
    ........................
    ........................

    I'd like to get just cod with more name:

    I tried this
    SELECT COUNT(NAME) TOT, COD
    FROM OCC
    GROUP BY COD
    HAVING COUNT(NAME) > 1

    OK, I get this:

    TOT..........COD
    2............001
    3............003
    2............005

    but I'd like to get also the records.

    For example I want this (only the records with more name):

    COD.............NAME
    001.............TOM
    001.............SAM
    003.............DOG
    003.............SAM
    003.............TOM
    005.............TOM
    005.............SAM

    How can I write this query??

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90

    inline view

    select
    s.tot, o.cod, o.name
    from
    occ o,
    (select count(name) tot, cod
    from occ
    group by cod
    having count(name) > 1) s
    where
    o.cod = s.cod

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Bet this is faster ...
    Code:
    Select
       cod,
       name
    From
       (
       Select
          cod,
          name,
          Count(*) Over (Partition By cod)
             row_per_cod
       From
          OCC
       )
    Where
       row_per_code > 1
    ...or...
    Code:
    Select
       cod,
       name
    From
       (
       Select
          cod,
          name,
          Count(distinct name) Over (Partition By cod)
             name_per_cod
       From
          OCC
       )
    Where
       name_per_code > 1
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    slim, you are the $h!t. Awesome code.

    Can you use analytical to improve on this:
    http://www.dbasupport.com/forums/sho...threadid=42387

    I know you can.
    Last edited by ddrozdov; 04-19-2004 at 10:39 AM.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by ddrozdov
    Can you use analytical to improve on this:
    http://www.dbasupport.com/forums/sho...threadid=42387
    Yes.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    You're my idol.

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