CODEWITHSUNDEEP
sqlsql-server
Koosh
Koosh

Reputation: 896

count number of unduplicated clients with records

I have a table with a bunch of information. I'm looking to find an unduplicated count of clients with 1 or 2 occurances of payments. here's the basic structure:

ClientID   PmtLn PaymentNum    vNum         
1          1     15            1234
1          2     -15           1234
2          1     25            3456
2          1     30            8583
3          1     50            9992
3          1     74            1230
4          1     15            3345

Each client in this table has received some type of payment. ClientID =1 - for example received a payment of 15 but then in PmtLn =2, that payment was negated, so since it's 0 it doesn't count. The other two client have only positive payments, so I'm hoping to be able to find the count of unduplicated clients that have only 1 or 2 payments (but unduplicated)

So my end result would look like this:

 2 payments
 ClientID 
 2
 3

 1 payment
 ClientID
 4

both clients 2 & 3 have 2 payments, that were made. Client 4 only has 1 payment therefore should be only shown in the 1 payment result. Client 1 - has 2 payments - but one was positive, and one was negative therefore it's at 0 and shouldn't be ocunted. Any help is appreciated.

Upvotes: 0

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

If I understand correctly:

select clientid
from t
group by clientid
having sum(case when payment > 0 then 1 else 0 end) = 2 and
       count(*) = 2;

Upvotes: 1

Related Questions