CODEWITHSUNDEEP
sqlsql-serversql-server-2008duplicates
skmasq
skmasq

Reputation: 4511

SQL Find duplicates against specific records

Edit: It is MS SQL Server 2008

I want to find duplicates against only specific records, in example below I want to find duplicates against records that have Status = 1

Here is example data set

ID  |Name   |Status 
------------------------
1   |ABC    |1
2   |BAC    |1
3   |CBA    |1
4   |ABC    |2
5   |BAC    |5
6   |BAC    |7
7   |DAE    |8
8   |DAE    |2

What I want to get is this

Name    |Count  
-----------------
ABC     |2
BAC     |3

Originally I thought to use this

SELECT      Name,COUNT(*)
GROUP BY    Name
HAVING      COUNT(*) > 1

But the result would be

Name    |Count  
-----------------
ABC     |2
BAC     |3
DAE     |2

But that's not what I need.

Upvotes: 0

Views: 161

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You are close. You want to change the having clause to just count values where status is 1:

SELECT      Name, COUNT(*)
FROM table t
GROUP BY    Name
HAVING      sum(case when status = 1 then 1 else 0 end) > 0;

EDIT:

If you only want things with a count greater than 1 as well as a status of 1:

SELECT      Name, COUNT(*)
FROM table t
GROUP BY    Name
HAVING      sum(case when status = 1 then 1 else 0 end) > 0 and
            count(*) > 1;

Upvotes: 1

Related Questions