CODEWITHSUNDEEP
mysqlduplicate-data
Steve Martin
Steve Martin

Reputation: 329

How to find duplicate email within a mysql table

I want to fetch duplicate email from table:

userid      email
-------------------------
1       [email protected]
2       [email protected]
3       abc%40gmail.com
4       [email protected]
5       abcd%40gmail.com

So from above records i want result like

Email          Count
-------------------------
[email protected]   2
[email protected]  2
[email protected]   1

Does anybody know how to manage that?

Thanks.

Upvotes: 6

Views: 13094

Answers (3)

Swapnil Patil
Swapnil Patil

Reputation: 31

Here is a simple solution:

SELECT email, COUNT(1) FROM table_name GROUP BY email HAVING COUNT(1) > 1

Upvotes: 3

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

You can't directly do that in MySQL because there is no function to urlencode or urldecode strings.

You will have to create a User Defined Function to handle that process. Once you have that function just go for a simple group by with a having clause.

Link to the required UDFs

If UDFs are not an option, the only workaround I can think of is manually replacing the chars (under your own risk):

SELECT REPLACE(email, "%40", "@") DuplicateEmail, COUNT(*) Amount
FROM t
GROUP BY DuplicateEmail
ORDER BY Amount desc

Fiddle here.

Output:

| DUPLICATEEMAIL | AMOUNT |
---------------------------
|  [email protected] |      2 |
| [email protected] |      2 |
|  [email protected] |      1 |

Upvotes: 4

derhansen
derhansen

Reputation: 6133

If you want to output the data exactly like shown in your question, use this query:

SELECT email, COUNT(*) AS count
FROM table
GROUP BY email HAVING count > 0
ORDER BY count DESC;

Upvotes: 13

Related Questions