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

By Hardik Savani | January 14, 2016 | | 1397 Viewer | Category : PHP 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...


Do you want to expert php developer or developer team for your projects ?
Contact US