How to count unique domain from email address field MySQL?

Share Me
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

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.

About code chef

My name is Shahriar sagor. I'm a developer. I live in Bangladesh and I love to write tutorials and tips that will help to other Developer's. I am a big fan of PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.

View all posts by code chef →