Get Email Count Based On Email Domain In MYSQL.
Hello Guys,
Hope You all are doing well. Today We are going to learn about Some MYSQL Query hacks.
Let's Suppose we need to count the emails on the base of the domain name in MySQL. How we can do this? In PHP we can easily count the emails based on the domain using explode function. But this is not a good method to count the domains of there is a lot of data.
It will be good if we count the domains on MYSQL. We can do this using the SUBSTRING_INDEX() function in MYSQL.
SUBSTRING_INDEX()
The SUBSTRING_INDEX() function returns a substring of a string before a specified number of delimiter occurs. SUBSTRING_INDEX() functions take 2 arguments string, delimiter, and number. The number can be positive or negative. All three parameters are required. The string is the original value, we can use the column name in the string value, second is the delimiter. Which will be used to subtract the string and last is the Number. The number of times to search for the delimiter. Can be both a positive or negative number. If it is a positive number, this function returns all to the left of the delimiter. If it is a negative number, this function returns all to the right of the delimiter.
I will show you how we can the email count based on the domain with the example.
+++++++++++++++++++++++++++++++++++++++++
+ Id emails +
+ 1 cj@cj.com +
+ 2 admin@admin.com +
+ 3 teacher@teacher.com +
+ 4 parent@parent.com +
+ 5 accountant@accountant.com +
+ 6 admin1@admin.com +
+ 7 teacher1@teacher.com +
+ 8 accountant1@accountant.com +
+ 9 parent1@parent.com +
+ 10 admin2@admin.com +
+ 11 teacher2@teacher.com +
+ 12 accountant2@accountant.com +
+ 13 parent2@parent.com +
+ 14 admin3@admin.com +
+ 15 teacher3@teacher.com +
+ 16 accountant3@accountant.com +
+ 17 parent3@parent.com +
+ 18 student@student.com +
+ 19 niko.marquardt@example.com +
+ 20 feest.irving@example.com +
+ 21 vstehr@example.org +
+ 22 cronin.boyd@example.net +
+ 23 oliver.towne@example.com +
+ 24 casey34@example.org +
+ 25 emily.batz@example.com +
+++++++++++++++++++++++++++++++++++++++++
MySQL Query
SELECT SUBSTRING_INDEX(email, '@', -1) as domain, count(*) as email_count FROM users GROUP BY domain ORDER BY email_count DESC
Result
+++++++++++++++++++++++++++++++++++++
+ domain email_count +
+ example.net 13 +
+ example.com 12 +
+ example.org 11 +
+ accountant.com 4 +
+ admin.com 4 +
+ parent.com 4 +
+ teacher.com 4 +
+ cj.com 1 +
+ student.com 1 +
+++++++++++++++++++++++++++++++++++++
Hope this post is helpful to you.
Thanks
Templatebench
We are Template bench. We love programming. We also love to share tips and tricks with you.