[RESOLVED] need to get a count

**nrana** · 02-23-2009, 02:05 PM

i have field like this :

Email
---------
abc@hotmail.com
xyz@hotmail.com
abc@yahoo.com
xyz@yahoo.com
abc@aol.com

is there anyway to get a count on that field based on domain name. For eg
how many has '@hotmail.com' or '@yahoo.com'. Buliding different query for each one is not an option for me..

Thanks you in advance..

**Charles** · 02-23-2009, 02:11 PM

You can group by substrings. However, string functions vary from server to server. Which one are you using?

**nrana** · 02-23-2009, 02:12 PM

sorry i should have mentioned that..
i am suing sql server 2005

**Charles** · 02-23-2009, 02:17 PM

Something like:

Code:

select substring ("Email", charindex ('@', "Email"), length ("Email") - charindex ('@', "Email")) as "Domain, count (*) as "Num"
from TABLE
group by substring ("Email", charindex ('@', "Email"), length ("Email") - charindex ('@', "Email"))

**nrana** · 02-23-2009, 02:29 PM

works like a charm... thank you so much Charles
just a quick small thing on it.. when i run the query, the last character of the field is cutoff
for eg: it shows like '@hotmail.co'
something we can do to quick fix that?
really appreciate it.

**Charles** · 02-23-2009, 02:40 PM

Get yourself a good SQL reference. I'm using O'Reilly's SQL Pocket Guide. Or dig around the internet and find a list of the SQL Server functions. http://msdn.microsoft.com/en-us/library/ms187748.aspx You'll note that the third parameter passed to substring specifies the length of the substring returned. Try instead

Code:

substring ("Email", charindex ('@', "Email"), length ("Email")) as "Domain, count (*) as "Num"
from TABLE
group by substring ("Email", charindex ('@', "Email"), length ("Email"))