SQL to display the total number of domain names from an email column

Question

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

Table:

I have SQL:

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

But he gives the result by email. How:

 EMail Expr1001 [e-mail] 4 [e-mail] 3 [e-mail] 2 

and etc.

How to show domains and their total score? How:

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

and etc.

I am using Access 2013.

Answer 1

MS Access has two functions, in particular, that help. You should basically do this:

• Extract the part of the domain that appears after the "@" char. (Help Mid and InStr help with this.)
• Use this with a counter.

In MS Access you can do this:

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

To calculate these usage values ​​as usual.

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

Now, if you need an SQL server for MSSQL:

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

Answer 2

In SQL, you could simply 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;