Postgres Get Count of Email Domains

Question

If I have user email entries in my db and I want to see how many users have the domain @gmail.com and how many users have the domain @yahoo.com and so on. What would be the best way to do that?

Right now I am going through and running the query manually one by one by doing:

select email from "user" where email ilike '%@gmail.com';

Answer 1

My first idea would be to extract the domain and group by that:

select substring(email from '@(.*)$') as domain, count(*)
from "user"
group by domain

Answer 2

In this case function split_part can be used:

select split_part(email, '@', 2) "domain", count(*)
from emails 
group by "domain";

PostgreSQL split_part fiddle