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,279
    You can group by substrings. However, string functions vary from server to server. Which one are you using?
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  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,279
    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"))
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  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,279
    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"))
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  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,279
    Try that link I posted.
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  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