[RESOLVED] need to get a count

[nrana]

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]

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

[nrana]

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

[Charles]

Something like: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]

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]

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 substring ("Email", charindex ('@', "Email"), length ("Email")) as "Domain, count (*) as "Num"
from TABLE
group by substring ("Email", charindex ('@', "Email"), length ("Email"))

[nrana]

super..that worked.
i tried msdn but didn't got much help there.. i will try O'Reilly's SQL Pocket Guide
thanks again for you help :) i'm on my learning phase.

[Charles]

Try that link I posted.

[nrana]

i will do..
Thanks again