SQL Find/Count similar duplicates-CodePudding

I am trying to to do a count on all records that are similar. Eg.

I have 2 records 1 is 1A Smith St and the other is 1 Smith St. So this data needs to be returned/counted as 1.

Any assistance would be gratefully appreciated.

Thanks

CodePudding user response：

Counting those where the (street, number) combination isn't unique.

SELECT COUNT(*) AS Total
FROM
(
    SELECT Street, Number
    FROM your_table
    GROUP BY Street, Number
    HAVING COUNT(*) > 1
) q

CodePudding user response：

use

SELECT COUNT(1) AS NUMBER, Suffix, Street FROM TableName GROUP BY Suffix, Street