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
[email protected]  4
[email protected]  3
[email protected]   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)