Rows with duplicate column values

Question

I am pretty sure there are rows with duplicate column values in the table:

SELECT TenancyReferralKey, FromDate, ToDate, ToDate_Value, ReferralID, ReferralFor, ReferralStatus FROM dm.Dim_TenancyReferral WHERE ReferralID IN ('1138', '1940', '1946') ORDER BY ReferralID

And I am trying to count the number of rows with the duplicated ReferralID:

SELECT TenancyReferralKey, FromDate, ToDate, ToDate_Value, ReferralID, ReferralFor, ReferralStatus, COUNT(*) [Occurrences] FROM dm.Dim_TenancyReferral GROUP BY TenancyReferralKey, FromDate, ToDate, ToDate_Value, ReferralID, ReferralFor, ReferralStatus HAVING COUNT(*) > 1

But getting empty result set.

Thanks for your help.

Dale K · Accepted Answer · 2020-09-24 03:31:33Z

You are grouping by a number of non-unique columns (TenancyReferralKey, FromDate, ToDate, ToDate_Value). If you remove those you will receive the duplicates you are after e.g.

SELECT ReferralID, ReferralFor, ReferralStatus, COUNT(*) [Occurrences] FROM dm.Dim_TenancyReferral GROUP BY ReferralID, ReferralFor, ReferralStatus HAVING COUNT(*) > 1

Greg · Accepted Answer · 2020-09-24 03:32:13Z

Edit the COUNT statement in your query. COUNT(*) [Occurrences] counts the number of rows in the table when grouped by all of the columns in the GROUP BY statement. So every row has its own set of values (there are no cases where the entire record is a duplicate). Try

WITH cte AS ( SELECT TenancyReferralKey, FromDate, ToDate, ToDate_Value, ReferralID, ReferralFor, ReferralStatus, COUNT(*) OVER (PARTITION BY ReferralID) AS Occurences FROM dm.Dim_TenancyReferral ) SELECT * FROM cte WHERE Occurences > 0 ;

Thanks for that elaboration and showing CTE. I will look at the OVER and PARTITION BY keywords.

Collectives™ on Stack Overflow

Rows with duplicate column values

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related