Documentation Visual SQL (beta) FAQs

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 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.

  1. Once we have our initial query, we add a Calculated 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.

    Extract the domain from the emails

  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.

    Use Group & Aggregate to Group by Domain and get a Total Sum of the Event Ids

  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:

Final chart for grouping by domain