SQL Query for getting distinct domain and count

Question

I have a Data Extension which has email address. I want to store all distinct domains and counts of these emails in another DE For eg: I have someth@som.com, test@test.com, 123@test.com. So my o/p should be: som.com 1 test.com 2

Thanks

Answer 1

Not being an SQL guru, I can't tell you whether this is the most efficient way of fetching and counting all the unique domains, but it at least does the job:

SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) as emaildomain ,
COUNT(Email) as EmailCount
FROM   [Your data extension]
WHERE  LEN(Email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY EmailCount DESC

Which will give you this output (in a bad example, with only gmail.com addresses in the DE):