CODEWITHSUNDEEP
mysql
Abdulrahman Al-Qabandi
Abdulrahman Al-Qabandi

Reputation: 11

Mysql: how to generate count table by PATTERN / LIKE statement ? very tricky

to simplify, I got a database of registered users, I want to count how many emails there are for each email domain name (note I do not know all domain names)

for example, Users table-

id    |    email
------------------
1     | [email protected]
2     | [email protected]<--unknown to me
3     | [email protected]<--unknown to me
4     | [email protected]
5     | [email protected]<--unknown to me
6     | [email protected]
7     | [email protected]
8     | [email protected]

( note: I want to count each email without specifying which email exactly )

so the result I want is

suffix         |   count
hotmail.com    |    3
somesite.aaa   |    2
unknownsite.aaa|    1
yahoo.com      |    2

again, I stress this, I do not know unknownsite.aaa nor can i mention it in a statement because it is unknown to me, i hope I am clear.

So essentially I want to make a statistic of what my website users use as an email host website. but like I said and I will repeat the third time, I do not know every mailhost that exists.

I am going to investigate this more, I have a feeling this is something mysql cannot handle.

EDIT:

I came up with the following solution, but seems tiny bit redundant, which I hate :P

select substring_index(`email`,'@',-1),count(*) as count from users group by substring_index(`email`,'@',-1) order by count ASC;

Upvotes: 0

Views: 339

Answers (2)

eggyal
eggyal

Reputation: 125865

SELECT   SUBSTRING_INDEX(email, '@', -1) AS suffix, COUNT(*) AS count
FROM     users
GROUP BY suffix

See it on sqlfiddle.

Upvotes: 3

mahen3d
mahen3d

Reputation: 7754

use RLIKE or REGEXP in mysql

http://dev.mysql.com/doc/refman/5.0/en/regexp.html

Upvotes: 0

Related Questions