Rows with duplicate column values

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.

Answer

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

Advertisement

Answer