# Thread: Need Help with Simple Count Function

1. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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```

3. Registered User
Join Date
Jun 2013
Posts
5
Originally Posted by gvee
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. Registered User
Join Date
Jan 2013
Posts
355

## 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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Clever Joe.
I've only ever used the row aggregate functions with CTEs.

6. Registered User
Join Date
Jan 2013
Posts
355

## 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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595