6

I want to fetch duplicate email from table:

userid      email
-------------------------
1       abc@gmail.com
2       abcd@gmail.com
3       abc%40gmail.com
4       xyz@gmail.com
5       abcd%40gmail.com

So from above records i want result like

Email          Count
-------------------------
abc@gmail.com   2
abcd@gmail.com  2
xyz@gmail.com   1

Does anybody know how to manage that?

Thanks.

Steve Martin
  • 319
  • 2
  • 4
  • 10

3 Answers3

13

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;
derhansen
  • 5,585
  • 1
  • 19
  • 29
  • 1
    Just a friendly note for whoever flagged this: while this is not the CORRECT answer, it is AN answer - that's what downvotes are for. Flagging "not an answer" is for when someone adds a comment (e.g. "me too") as an "answer". Cheers! – Jeffrey Kemp Aug 15 '13 at 07:14
  • The above by @derhansen is how I de-dupe tables. I will sometimes LOWER(`email`) incase someone used upper & lower case when entering their address. I also only show the duplicate records with count > 1. Works well. – backwardm Dec 11 '14 at 18:14
4

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 |
---------------------------
|  abc@gmail.com |      2 |
| abcd@gmail.com |      2 |
|  xyz@gmail.com |      1 |
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • No i want to fetch both records that contains @ & %40 – Steve Martin Aug 15 '13 at 07:04
  • That is not what you have in your example... you are only displaying the ones with the @ in your result. The output of my query is what you are looking for in your expected result. Please, update your question to display the output you are now saying you need. – Mosty Mostacho Aug 15 '13 at 07:08
3

Here is a simple solution:

SELECT email, COUNT(1) FROM table_name GROUP BY email HAVING COUNT(1) > 1
Julian
  • 886
  • 10
  • 21
Swapnil Patil
  • 31
  • 2
  • 1
    Welcome to Stack Overflow. If you decide to answer an older question that has well established and correct answers, adding a new answer late in the day may not get you any credit. If you have some distinctive new information, or you're convinced the other answers are all wrong, by all means add a new answer, but 'yet another answer' giving the same basic information a long time after the question was asked usually won't earn you much credit. What you've provided is equivalent to an existing answer, and doesn't deal with the URL encoding of the data. It isn't helpful, therefore. – Jonathan Leffler Mar 10 '20 at 06:36