How to count unique domains from email address field in MySQL ?
In this post we will give you information about How to count unique domains from email address field in MySQL ?. Hear we will give you detail about How to count unique domains from email address field in MySQL ?And how to use it also give you demo for it if it is necessary.
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…
Hope this code and post will helped you for implement How to count unique domains from email address field in MySQL ?. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs