SQL for showing total count of domain names from email column

Question

I have an email column (duplicates-ok) in MS Access table from which I want to show all domain names (from domain part of email) and their counts in MS Access.

Table:

I have SQL:

SELECT EMail.EMail, COUNT(*)
FROM EMail
GROUP BY EMail.EMail
ORDER BY COUNT(*) DESC;

But it gives result based on email. Like:

EMail   Expr1001
XXX@googlemail.com  4
YYY@googlemail.com  3
AA@argpub.com   2

etc.

How do I show domains and its total count? Like:

gmail.com 10
yahoo.com 5
yahoo.co.in 3

etc.

I am using Access 2013.

Answer 1

In SQL You could just do this:

SELECT SUBSTRING_INDEX(EMail.EMail, '@', -1) AS `Email Domain`, COUNT(*)
FROM EMail
GROUP BY SUBSTRING_INDEX(EMail.EMail, '@', -1)
ORDER BY COUNT(*) DESC;

Answer 2

MS Access has Two Functions in particular which help. You have to basically do this:

• Extract the domain part which appears after '@' char.(Mid and InStr Function help with this.)
• Use this along with the count.

In MS Access you could do this:

Mid([Email],InStr([Email],"@")+1) which will give you the domain names.

To count these use count normally as you would.

Refer : http://www.techonthenet.com/access/functions/string/mid.php

Now if you need the SQL for MSSQL server:

select SUBSTRING(email,(CHARINDEX('@',email)+1),1), count(*) from ...(rest of your query)

Answer 3

This similar question has been asked me in an interview and solution I got is:

SELECT RIGHT(Email, LEN(Email)-CHARINDEX('@',Email)) AS Domain, COUNT(Email) AS Count 
FROM tblEmails
Group By RIGHT(Email, LEN(Email)-CHARINDEX('@',Email))
ORDER BY Count ASC;

Hope this will help somebody.