Results 1 to 5 of 5

Thread: count on table

  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: count on table

    I 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 2002
    Posts
    56
    SELECT COUNT(1) TOT, COD,NAME
    FROM OCC
    GROUP BY COD,NAME
    HAVING COUNT(1) > 1

  3. #3
    Join Date
    Apr 2002
    Posts
    56
    Sorry, I think I misunderstood the question.

    SELECT *
    from OCC
    ,
    (
    SELECT COUNT(1) TOT, COD
    FROM OCC
    GROUP BY COD
    HAVING COUNT(1) > 1
    ) iv
    where iv.cod = occ.cod
    order by cod,name

    This will give you all the record with COD's from your first query.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by edwin
    SELECT COUNT(1) TOT, COD,NAME
    FROM OCC
    GROUP BY COD,NAME
    HAVING COUNT(1) > 1
    No, that will return no rows at all for the sample data given.

    There are logically 2 steps to this:

    1) Which codes have more than 1 name?
    2) What are the names associated with those codes?

    This query will answer that:
    PHP Code:
    select codname
    from occ
    where cod in
    select cod
      from occ
      group by cod
      having count
    (name) > 1
    ); 

  5. #5
    Join Date
    Apr 2002
    Posts
    56
    I realised the error and was already typing (see second reply), but not quick enough.

Posting Permissions

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