Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    Unanswered: Running Count by Group

    I'm starting to think I can't do this with a SELECT....but, any input would be great!

    I'm trying to get a running count by group....meaning, my data looks like this (two columns):

    john.doe@yahoo.ca X
    john.doe@yahoo.ca Y
    john.doe@yahoo.ca M
    elvis@gmail.ca A
    kid.rock@hot.ca X
    kid.rock@hot.ca Y

    ..and I want to add a column like this (running count of codes by email address):

    john.doe@yahoo.ca X 1
    john.doe@yahoo.ca Y 2
    john.doe@yahoo.ca M 3
    elvis@gmail.ca A 1
    kid.rock@hot.ca X 1
    kid.rock@hot.ca Y 2


    thoughts? ...thanks in advance.
    david.
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What version of SQL Server?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sql server 2005 --
    Code:
    select email
         , letter
         , row_number() over 
             ( partition by email 
                   order by letter ) as rownum   
      from daTable
    order
        by email
         , letter
    sql server 2000 --
    Code:
    select email
         , letter
         , ( select count(*) + 1
               from daTable
              where letter < T.letter ) as rownum
      from daTable as T
    order
        by email
         , letter
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    thanks...

    yup, that does the trick. much appreciated
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

Posting Permissions

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