Mysql: how to generate count table by PATTERN / LIKE statement ? very tricky
- by Abdulrahman Al-Qabandi
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     | test@hotmail.com
2     | [email protected]<--unknown to me
3     | [email protected]<--unknown to me
4     | test@hotmail.com
5     | [email protected]<--unknown to me
6     | test@yahoo.com
7     | test@yahoo.com
8     | test@hotmail.com
( 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.