SQL for showing total count of domain names from email column
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.
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;
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)