Home Laravel How to count unique domain from email address field MySQL?

How to count unique domain from email address field MySQL?

by Shahriar Sagor

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 DESC

output:

+----------------+----------------+

| 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.

close

You may also like