group by help

• Tara-1044200

  SSCoach

  Points: 15951

  More actions

  April 3, 2017 at 1:47 pm

  #322745

  from the following sample record , every userid has an under score in the middle which gives session id but i would like to exclude the session and get count for the user account and group by year. I would like to see count for each user per month and year.

  userid                                                                  timestmap                         
  Domain\mpollin_qspmmk45vnd3uheooa3dst45      12/21/2016 13:33:16.740000   

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 3, 2017 at 2:04 pm

  #1936415

  Tara-1044200 - Monday, April 3, 2017 1:47 PM

  from the following sample record , every userid has an under score in the middle which gives session id but i would like to exclude the session and get count for the user account and group by year. I would like to see count for each user per month and year.

  userid                                                                  timestmap                         
  Domain\mpollin_qspmmk45vnd3uheooa3dst45      12/21/2016 13:33:16.740000   

  You could use something like this:
  GROUP BY LEFT( userid, CHARINDEX( '_', userid))

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2