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 the Digisign2 schema in our Document Company 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.
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
Next, we hide the “Email” column, since we don’t want to display it in our final chart.
We want our “Domain” column to be the leftmost column in our result set, so we reorder the columns accordingly.
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.
For a cleaner look, we rename the “SUM(Count of distinct Events Id)” column to
Here’s what our final chart looks like: