mysql count duplicates

Question

I have a table like this

mysql> desc user_changes;
+----------+--------------+------+-----+---------+-------+ 
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+ 
| id       | varchar(16)  | NO   | PRI |         |       | 
| email    | varchar(255) | YES  | MUL | NULL    |       |  
| products | longtext     | YES  |     | NULL    |       | 
+----------+--------------+------+-----+---------+-------+ 
3 rows in set (0.00 sec)

And i need to create a query that will count all the duplicate by email like this

 | 20061129180346 | [email protected] | 1^31^9 
 | 20061129330638 | [email protected] | 1^31^9

Did you need help with something or do you just want others to do it for you? — Michael Todd
– Michael Todd, Commented Feb 25, 2011 at 16:50

DVK · Accepted Answer · 2011-02-25 16:51:46Z

10

SELECT    count(*), email
FROM      user_changes
GROUP BY  email
HAVING    count(*) > 1

The last (HAVING) clause limits the selection to email counts that are >1, e.g. duplicates.

answered Feb 25, 2011 at 16:51

DVK

130k33 gold badges219 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mellamokb · Accepted Answer · 2011-02-25 16:51:51Z

1

select
    email,
    count(email) as email_count
from
    user_changes
group by
    email
having
    count(email) > 1
order by
    email asc

answered Feb 25, 2011 at 16:51

mellamokb

56.8k12 gold badges111 silver badges138 bronze badges

Comments

David Gillen · Accepted Answer · 2011-02-25 16:51:59Z

1

I don't understand your second block, but to get a count of each email do

SELECT email, COUNT(*) AS count FROM user_changes GROUP BY email WHERE count > 1;

answered Feb 25, 2011 at 16:51

David Gillen

1,1725 silver badges14 bronze badges

Comments

Martin Smith · Accepted Answer · 2011-02-25 16:52:05Z

1

select email, count(*)
from user_changes
group by email
having count(*) > 1

answered Feb 25, 2011 at 16:52

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Comments

BG100 · Accepted Answer · 2011-02-25 17:13:47Z

1

This will count how many of each email address there is in the table, disregarding the case of the email address. For example, "[email protected]" and "[email protected]" will be treated as the same email address. It also just shows the duplicates, any email address that just occurs once will not be returned. If you want to return these as well, just omit the "HAVING" clause.

SELECT LOWER(email) EmailAddress, COUNT(*) EmailCount
  FROM user_changes
 GROUP BY LOWER(email)
HAVING COUNT(*) > 1;

edited Feb 25, 2011 at 17:13

answered Feb 25, 2011 at 16:52

BG100

4,5412 gold badges41 silver badges66 bronze badges

Collectives™ on Stack Overflow

mysql count duplicates

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related