Group email addresses by domain in Visual SQL

Email addresses are commonly used as identifiers in databases and 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 super easy in Visual SQL.

For this example, we use our Dundersign Demo Data data source and add the Email column from the Users table and the Id column from the Events table. To get the number of events performed by each user, we selected Group for the Email column and Count of distinct for the Id column in the Result Table.

1. Once we have our initial query, we add a Formula Column, select Custom for the formula type, and use SQLite’s substr function to extract the domain from each email address:

    substr("Email Column Name",instr("Email Column Name", '@') + 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.

   For clarity, we rename our new column to Domain.

   

2. Next, we hide the “Email” column, since we don’t want to display it in our final chart.

3. We want our “Domain” column to be the leftmost column in our result set, so we reorder the columns accordingly.

4. Finally, we want to group our values by the email domain, so we add a Group & Aggregate Action. We Group by the “Domain” column and get the Total sum of the “Count of distinct Events Id” column, which returns a total sum of events grouped by the email domain.

   

5. For a cleaner look, we rename the “SUM(Count of distinct Events Id)” column to Total Events.

Here’s what our final chart looks like: