· 10 years ago · Jul 15, 2016, 08:45 AM
1SELECT email, count(email) FROM emails GROUP BY email HAVING count(email) > 1
2
3CREATE TABLE table1
4 (`email` varchar(255))
5;
6
7INSERT INTO table1
8 (`email`)
9VALUES
10 ('foo@gmail.com'),
11 ('bar@gmail.com'),
12 ('root@mail.ru')
13;
14
15select count(1), SUBSTRING_INDEX(email, '@', -1) as domain
16from table1
17group by domain
18
19| count(1) | domain |
20|----------|-----------|
21| 2 | gmail.com |
22| 1 | mail.ru |