CODEWITHSUNDEEP
mysql
anicapen
anicapen

Reputation: 19

How to count email addresses grouped by domain?

Given email addresss data like this, I need to display the number of users in each domain.:

+----------------------+
| email                |
+----------------------+
| [email protected] |
| [email protected]  |
| [email protected]   |
| [email protected]     |
| [email protected]        |
| [email protected]    |
| [email protected]  |
| [email protected] |
| [email protected]  |
| [email protected] |
+----------------------+

I want output like this:

+----------------------+-------------+
| Email Acount         | Total Users |
+----------------------+-------------+
| gmail.com            | 3           |
| lycos.com            | 1           |
| yahoo.com            | 6           |
+----------------------+-------------+

How should I do that in MySQL?

Upvotes: 1

Views: 184

Answers (1)

Barmar
Barmar

Reputation: 780929

SELECT SUBSTRING_INDEX(email, '@', -1) AS `Email Acount`, COUNT(*) AS `Total Users`
FROM YourTable
GROUP BY `Email Acount`

MySQL SUBSTRING_INDEX function documentation

DEMO

Upvotes: 3

Related Questions