CODEWITHSUNDEEP
sqlsql-servert-sql
Mike Marks
Mike Marks

Reputation: 10139

SQL counting email domains from email addresses

The intent of my query is to count the number of associated email addresses that have a particular domain. I think though that I have this reversed from what it should be because my result set produces a zero count for both fields.

Email address might look like: [email protected]. I want to do a count of all email addresses that share a Microsoft email domain (and same for AOL/Yahoo). I'm not sure where to go from here - any ideas?

select
    count(case when Email in ('%microsoft.com','%live.com','%outlook.com','%hotmail.com','%msn.com','%passport.com') 
        then Email end) as 'Microsoft',
    count(case when Email in ('%yahoo.com','%ymail.com','%yahoomail.com','%aol.com','%cs.com','%netscape.com','%verizon.net','%verizon.com','%bellatlantic.net','%gte.net','%managedmail.com') 
        then Email end) as 'AOL_Yahoo',
    '2021-02-15' as [Date]
from
    IP_Warming_Day_1_New_Registrants

Upvotes: 0

Views: 373

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I would split off the domain for this purpose:

select sum(case when v.domain in ('%microsoft.com', '%live.com', '%outlook.com', '%hotmail.com', '%msn.com', '%passport.com') 
                then 1 else 0
           end) as Microsoft,
       sum(case when v.domain in ('yahoo.com', 'ymail.com','yahoomail.com','aol.com','cs.com','netscape.com','verizon.net','verizon.com','bellatlantic.net','gte.net','managedmail.com') 
               then 1 else 0
           end) as AOL_Yahoo,
       '2021-02-15' as [Date]
from IP_Warming_Day_1_New_Registrants nr cross apply
     (values (stuff(nr.email, 1, charindex('@', nr.email), '')) ) v(domain)

You can also use like, but that is more cumbersome:

select sum(case when v.email like '%@microsoft.com' or
                     v.email like '%@live.com' or
                     v.email like '%@outlook.com' or
                     v.email like '%@hotmail.com' or
                     v.email like '%@msn.com' or
                     v.email like '%@passport.com')
                then 1 else 0
           end) as Microsoft,

Upvotes: 1

Related Questions