www.webdeveloper.com
Results 1 to 9 of 9

Thread: [RESOLVED] need to get a count

  1. #1
    Join Date
    Jun 2008
    Posts
    33

    resolved [RESOLVED] need to get a count

    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..

  2. #2
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,277
    You can group by substrings. However, string functions vary from server to server. Which one are you using?

  3. #3
    Join Date
    Jun 2008
    Posts
    33
    sorry i should have mentioned that..
    i am suing sql server 2005

  4. #4
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,277
    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"))

  5. #5
    Join Date
    Jun 2008
    Posts
    33
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,277
    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"))

  7. #7
    Join Date
    Jun 2008
    Posts
    33
    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.

  8. #8
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,277
    Try that link I posted.

  9. #9
    Join Date
    Jun 2008
    Posts
    33
    i will do..
    Thanks again

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles