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

Answer 1

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.

Answer 2

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