PostgreSQL: How to Get Count of Email Domains

Often in PostgreSQL you may want to count the number of occurrences of email domains in a column.

You can use the following syntax to do so:

SELECT split_part(email_address, '@', 2) "domain", count(*)
FROM emp
GROUP BY "domain";

This particular example counts the number of occurrences of each unique email domain in the email_address column of the emp table.

Note that we use the split_part() function to first split each string in the email_address column based on where the @ occurs and then we return the second element (i.e. the domain) from this split.

The following example shows how to use this syntax in practice.

Example: Get Count of Email Domains in PostgreSQL

Suppose that we create the following table named emp that contains the email address of various employees at some company:

-- create table named 'athletes'
CREATE TABLE emp (
  id INTEGER,
  email_address TEXT
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, 'Andy12345@gmail.com');
INSERT INTO emp VALUES (0002, 'Bob104234@gmail.com');
INSERT INTO emp VALUES (0003, 'Chad19Robbins@yahoo.com');
INSERT INTO emp VALUES (0004, 'Doug34344@outlook.com');
INSERT INTO emp VALUES (0005, 'Ethan19023@outlook.com');
INSERT INTO emp VALUES (0006, 'FrankTeam223@gmail.com');

-- view all rows in table
SELECT * FROM emp;

 id |      email_address      
----+-------------------------
  1 | Andy12345@gmail.com
  2 | Bob104234@gmail.com
  3 | Chad19Robbins@yahoo.com
  4 | Doug34344@outlook.com
  5 | Ethan19023@outlook.com
  6 | FrankTeam223@gmail.com

Suppose that we would like to count the number of occurrences of each domain name in the email_address column.

We can use the following syntax to do so:

SELECT split_part(email_address, '@', 2) "domain", count(*)
FROM emp
GROUP BY "domain";

   domain    | count 
-------------+-------
 gmail.com   |     3
 outlook.com |     2
 yahoo.com   |     1

Notice that the resulting table contains a column that shows the unique domain names from the email_address column along with the total count of each domain name.

Feel free to use similar syntax to count the number of occurrences of each domain name in a column of your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: Extract Substring Before Specific Character
PostgreSQL: Extract Substring After Specific Character
PostgreSQL: How to Extract Substring Between Two Characters