SQL command to check Domain Emails

Question

I have a table with a list of emails. How do I retrieve the count value and the unique domain names from the table?

For example,

EmailTable
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

The above should ideally return something as follows:

count EmailDomain
2     yahoo.com
2     hotmail.com
1     gmail.com

possible duplicate of [SQL: How to get the count of each distinct value in a column?](http://stackoverflow.com/questions/7053902/sql-how-to-get-the-count-of-each-distinct-value-in-a-column) — APerson, Jun 29 '14 at 23:25

Gordon Linoff · Accepted Answer · 2014-06-30T11:08:46.350

3

You can do this with basic string manipulations and aggregation:

select substr(e.email, instr(email, '@') + 1) as domain, count(*)
from emails e
group by substr(e.email, instr(email, '@') + 1);

edited Jun 30 '14 at 11:08

answered Jun 30 '14 at 01:12

Gordon Linoff

You missed the closing parenthesis. But I think this is what you want to write `select substr(e.email, instr(email, '@') + 1) as domain, count(*) from emails e group by domain;` – cshu Jun 30 '14 at 03:44

1 Answers1