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