How to count unique domains from email address field in MySQL ?

you want to count domain from your email address field in mysql. if you are working on PHP then you could do easily by using explode function and counting domains, but that way is a very bad logic and it take max time on execution when you have lot of data. But we can do easily in MySQL by using SUBSTRING_INDEX().

SUBSTRING_INDEX() take a three arguments string, delimeter and number.string is a source of string, the delimeter to search for in the string and the number parameter will search for delimeter.If pass negative in third argument then it will everything from the left of the targeted delimiter is returned by the SUBSTRING_INDEX().

I am going to give example of how to get unique domain from email addesses.

emails table

+--------+--------------------------+
|   id   |        email             |
+--------+--------------------------+
|   1    |  user@gmail.com          |
|   2    |  user@yahoo.com          |
|   3    |  admin@gmail.com         |
|   4    |  admin@yahoo.com         |
|   5    |  superadmin@gmail.com    |
|   6    |  superadmin@yahoo.com    |
|   7    |  hd@xpro.com             |
|   8    |  admin@hotmail.com       |
|   9    |  user@hotmail.com        |
|   10   |  test@gmail.com          |
+--------+--------------------------+

mysql query

SELECT 
	SUBSTRING_INDEX(email, '@', -1) as domain_name, count(*) as total_emails 
FROM emails
GROUP BY domain_name 
ORDER BY total_emails DESC 

output

+----------------+----------------+
| domain_name    | total_emails   |
+----------------+----------------+
| gmail.com      |      4         |
| yahoo.com      |      3         |
| hotmail.com    |      2         |
| xpro.com       |      1         |
+----------------+----------------+

you can learn from this example...