Do you want to count domain from email address field in mysql in laravel application. if we are working on PHP then we could do easily find by using explode function and counting domains, but on that way is a very bad logic and it is take max time on execution when we have a lot of data. But we can do this easily in MySQL by using SUBSTRING_INDEX().
SUBSTRING_INDEX() function take a three arguments string, delimeter and number. So string is a source of string, the delimeter to search for in the string and then the number parameter will search for delimeter. If we pass the negative in third argument then it will take everything from the left of the targeted delimiter is returned by the SUBSTRING_INDEX().
So now i am going to give a example of how to get unique domain from email addresses.
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 DESCoutput:
+----------------+----------------+
| domain_name | total_emails |
+----------------+----------------+
| gmail.com | 4 |
| yahoo.com | 3 |
| hotmail.com | 2 |
| xpro.com | 1 |
+----------------+----------------+Read Also : How to use Union query with Laravel Eloquent?
Thanks for read this, I hope it will you. You can also follow us on Facebook.
