CODEWITHSUNDEEP
mysqlcountduplicates
Romil
Romil

Reputation: 13

Mysql count total only if duplicates email OR phone

I am trying to count total duplicate rows if email duplicates OR phone duplicates.

here is my table

email         phone  
[email protected]    2222222222
[email protected]    6666666666  
[email protected]    5656565656 
[email protected]    2222222222

the output should total rows has duplicate value phone or email

above duplicate, the count is 3

Upvotes: 0

Views: 357

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

Just find all duplicated emails and all duplicated phone numbers in the table and count the row if it contains a value from either;

SELECT COUNT(*) 
FROM Table1
WHERE email IN (SELECT email FROM Table1 GROUP BY email HAVING COUNT(*)>1)
   OR phone IN (SELECT phone FROM Table1 GROUP BY phone HAVING COUNT(*)>1)

An SQLfiddle to test with.

Upvotes: 2

Related Questions