Group email addresses by domain

Email addresses are commonly used as identifiers in databases. Knowing how to manipulate and group by email address domains can be very helpful in day-to-day analysis operations. Grouping a list of email addresses by domain is easy to do in Visual SQL.

For this example, we have a column called “Email”. To get the number of users by email domain, we first select Group for the “Email” column and Count of distinct for the “Customer Id” column.

1. Once we have our initial query, we add a “Apply formula” step, select Custom for the formula type, and use SQLite’sSUBSTRfunction to extract the domain from each email address:

   1 SUBSTR("Email",INSTR("Email", '@') + 1)

   Since the domain of an email address always follows the @symbol, we can use the @character to differentiate between the username and the domain of the email address.

   

2. For clarity, we rename our new column to “Email Domain”.

3. Finally, we want to group our values by the email domain, so we add a “Group & aggregate” step. We Group by the “Email Domain” column and select the Total sum of the “Count of distinct Customer Id” column to return the total number of customers by domain.

   

4. To clean up the column names, we rename the “SUM(Count of distinct Customer Id)” column to “Total Customers”.

That’s it! Here’s what our final chart looks like: