Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2013
    Posts
    5

    Unanswered: Need Help with Simple Count Function

    Hey guys,

    I've gone rusty on my SQL skills and so annoyed that I can't get the count function to work like I want.

    Basically, I want my query to list all SSNS that have more than one record in the table. I have this query:

    Code:
    SELECT SSN, name4, count(*) from [1099_PER]
    group by SSN, name4
    having count(SSN) > 1
    It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.

    For example

    SSN NAME4 COUNT
    123445555 WALTER - 4


    I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN. What am I missing here?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Join that resultset back to your table
    Code:
    SELECT *
    FROM   [1099_PER]
     INNER
      JOIN ( <your grouped query> ) As x
        ON x.ssn   = [1099_PER].ssn
       AND x.name4 = [1099_PER].name4
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2013
    Posts
    5
    Quote Originally Posted by gvee View Post
    Join that resultset back to your table
    Code:
    SELECT *
    FROM   [1099_PER]
     INNER
      JOIN ( <your grouped query> ) As x
        ON x.ssn   = [1099_PER].ssn
       AND x.name4 = [1099_PER].name4
    That's what I needed. Thanks!

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Another way

    WITH X (ssn, something_name, ssn_cnt)
    AS
    (SELECT ssn, something_name,
    COUNT(*) OVER (PARTITION BY ssn)
    FROM Per_1099
    GROUP BY ssn, something_name)

    SELECT ssn, something_name
    FROM X
    WHERE ssn_cnt > 1;

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Clever Joe.
    I've only ever used the row aggregate functions with CTEs.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Yet another way!

    WITH X (ssn, something_name, ssn_cnt, ssn_min, ssn_max)
    AS
    (SELECT ssn, something_name, MIN(ssn), MAX(ssn)
    FROM Per_1099
    GROUP BY ssn, something_name)

    SELECT ssn, something_name
    FROM X
    WHERE ssn_min < ssn_max;

    Or just:

    SELECT ssn, something_name
    FROM Per_1099
    GROUP BY ssn, something_name
    HAVING MIN(ssn) < MAX(ssn) ;

    The idea is that when MIN(ssn) = MAX(ssn), we have a singleton. I have no idea if either of these will run faster. I will guess that a clustered index would be the big factor.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've used that method before, but the previous CTE you posted is more extensible to N duplicates.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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