Identifying duplicate fields in a row

Transact-SQL
1

Hello all.

I am trying to identify when the MemberNumber, AcctNumber, LastFour, and RetrievalRefNumber are the same. I thought I could use something like:

Times = COUNT(Concat(aud.MemberNumber,aud.AcctNumber,aud.LastFour,aud.RetrievalRefNumber))

But, it doesn't count anything.

So in this example, the Times field s/b 2

image
image792×65 3.59 KB

Thank you

2 
SELECT MemberNumber, AcctNumber, LastFour, RetrievalRefNumber, COUNT(*) AS DupRowCount
FROM dbo.table_name
GROUP BY MemberNumber, AcctNumber, LastFour, RetrievalRefNumber
HAVING COUNT(*) > 1
3

Thanks Scott.

Is there any way to add additional fields that aren't necessarily duplicates?

I am trying to identify rows where MemberNumber, AcctNumber, Last Four, and RetrievalRefNumber are the same, but I want to see additional fields, like the Type, Memo, etc. that aren't necessarily the same.

Thanks for your help.

4 
SELECT *
FROM (
    SELECT *, COUNT( * ) OVER(PARTITION BY MemberNumber, AcctNumber, 
        LastFour, RetrievalRefNumber) AS dup_count
    FROM dbo.table_name
) AS dup_counts
WHERE dup_count > 1
5

Thanks for the reply Scott.

I just plopped this into my existing query and it worked.
COUNT( * ) OVER(PARTITION BY MemberNumber, AcctNumber,
LastFour, RetrievalRefNumber, LocalDate) AS dup_count