Bobski Bobski - 11 months ago 79
SQL Question

SQL Server - Select distinct Query

I have a tblA where I have a few thousand clients. I'm trying to write a query that would show me clients that have more than one record of a certain type. This is what my table looks like.

ClientID TypeB
123 1
145 1
123 2
199 1
199 2
145 2
123 1

So as you can see here (this is not a complete table, but this is only relevant). Each client is supposed to only have one record for TypeB=1 and TypeB=2. Some clients however(123) has more than one record for TypeB=1. I'm trying to find all clients that have more than one record of Type=1.

Desired end result:

ClientID TypeB
123 1
123 1

This is what I've been trying to do

select distinct(clientid), TypeB
from tblA
where TypeB=1
having count(TypeB)>1
group by clientid

Answer Source

I'm thinking you need something like this

SELECT COUNT(ClientID), ClientID, TypeB
WHERE TypeB = 1
GROUP BY ClientID, TypeB