1

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
abc@yahoo.com
abc1@yahoo.com
abc@hotmail.com
abc23@hotmail.com
def@gmail.com

The above should ideally return something as follows:

count EmailDomain
2     yahoo.com
2     hotmail.com
1     gmail.com
SamIAm
  • 2,241
  • 6
  • 32
  • 51
  • 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

1 Answers1

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);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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