Search code examples
sqlms-accessms-access-2013

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:
enter image description here

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.

Solution

  • 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)