Search code examples
sqlduplicatesansi-sql

Finding duplicate count in SQL

I know how to return duplicates by doing the following:

select cust_name, cust_id, count(*)
from customers
group by cust_name, cust_id
having count(*) > 1;

What I want to know if there is a way to return a straight count of duplicates in the table, and not the associated values?

For example if the above query returned:

cust_name   cust_id count(*)
Fred        22789      2
Jim         45678      3

Is there a query I could write that would just return the number?

So

count(*)
   2

Or

count(*)
   5

Something like that. In reality the uniqueness could be a combo of 1 to N columns, the above example just shows 2 columns.

Solution

  • You would use a subquery:

    select count(*) as num_with_duplicates,
       sum(cnt) as total_duplicate_count
from (select cust_name, cust_id, count(*) as cnt
      from customers
      group by cust_name, cust_id
      having count(*) > 1
     ) cc;